July 22, 2014 at 8:31 am
Hi,
I dropped a foreign key constraint on a table and now trying to add it back again.
However, I get an error while doing so - The ALTER TABLE statement conflicted with the FOREIGN KEY constraint - "fk_gftr_jhty". The conflict occurred in database "test".
Could someone please advice how to achieve this ?
Thanks.
July 22, 2014 at 11:17 am
This means that you have data in the table that violates the foreign key. You can still add the FK, but it will not be trusted, meaning that it can't be used for query optimization. You do this by adding WITH NOCHECK to the FK definition like this:
ALTER TABLE
WITH NOCHECK ADD CONSTRAINT [FK Name] FOREIGN KEY ([columns]) REFERENCES [referenced table]([columns])
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2014 at 11:43 am
But, doing what Jack is suggesting will mean that you still have data in the table that doesn't match the related table. I'd suggest tracking that data down and fixing it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2014 at 11:49 am
Grant Fritchey (7/22/2014)
But, doing what Jack is suggesting will mean that you still have data in the table that doesn't match the related table. I'd suggest tracking that data down and fixing it.
Oops meant to add that. That's what happens when real work interrupts the fun stuff:-D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply