Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing a script. The purpose of this script is to

  1. Get all foreign key constraints on a database.
  2. Truncate a table.
  3. Re-Add the foreign key constraints that were removed.

I'm having trouble adding the constraints again [step 3] and I believe this is to do with the fact that my constraint conflicts with what is available. The data initially is...

authors table

1   Kenneth Graham  k.graham@twitw.com

books table

1   The wind in the willows Classic childrens story about woodland animals  123456

Authorbooks table

1   1   1

I have the following constraints on the authorbooks tables:

ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Books] FOREIGN KEY([BookId])
REFERENCES [dbo].[Books] ([ID])
GO
ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Authors] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([Id])
GO

So when I try and add the constraints I get the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Books_Author_xref_Authors". The conflict occurred in database "PeteDb", table "dbo.Authors", column 'Id'.

Is there any way around this and being able to add the constraint in regardless?

share|improve this question
add comment

migrated from stackoverflow.com Nov 3 '11 at 16:56

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 5 down vote accepted

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. What's the difference between WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT and just ADD CONSTRAINT in SQL Server?

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.

share|improve this answer
    
MATE! thanks, saved me a great deal of dissappointment :-) Thougth all my hard work was for nothing.... –  Pete2k Nov 3 '11 at 16:45
add comment

Re-Add the foreign key constraints that were removed.

You need not recreate FKs, as the truncate table does not drop them, but only delete the rows.

share|improve this answer
    
You can't truncate a table that is referenced in a foreign key constraint; therefore you must drop the foreign key constraints first. Hence the need to re-add the foreign key constraints that were removed. –  user339227 Nov 8 '11 at 16:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.