what is best way to reload table without running into foreign key constraint error?

Question

what is best way to reload table without running into foreign key constraint error?

John-250225

Mr or Mrs. 500

Points: 588
More actions
August 3, 2005 at 10:24 am

#94385

Hello,
Anybody know the best way to clear out a table and repopulate it with new data and not get the foreign key constraint error?? I tried unchecking the "enforce relationship for INSERTS and UPDATES" check box and then trying to truncate the table and then I was going to DTS the new data in. But, that didn't work. Any ideas? Appreciate the help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

No good way. That's what the constraints are there for. You can script them out and drop them, reload, then recreate them.

John-250225 Mr or Mrs. 500 Points: 588 More actions · Answer 2

Hi Steve,

Yup that worked! I deleted the foreign keys on the table and then trucated all the tables and reloaded the data. Then recreated the foreign keys. Appreciate the help!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Anytime and thanks for the update.

Did you script them out? Just checking in case someone else searches the thread.

John-250225 Mr or Mrs. 500 Points: 588 More actions · Answer 4

actually i just did it through enterprise manager deleting the foreign keys and then just truncated the tables in query analyzer and then used Import and Export wizard to transfer the data over. Then recreated the foreign keys in enterprise manager again. probably work on the script later.. thanks steve!

steve block SSCertifiable Points: 7698 More actions · Answer 5

I have a script that will generate the Dropping and Recreating of Foreign Keys in the Script Library for an entire database.

http://www.sqlservercentral.com/scripts/contributions/1530.asp

Steve

Chrislis SSChasing Mays Points: 622 More actions · Answer 6

Why to drop the foreign key, when you could just disable them during the load of the new data and enable them again?

This seems the highly fashion way.

I've submitted a script on this site ("disable foregin keys")

steve block SSCertifiable Points: 7698 More actions · Answer 7

Actually, disabling and re-enabling foreign keys is the easiest way to go. I actually came up with the script because Red-Gates SQL-Compare has a bug in it that doesn't recognize that the foreign key has been re-enabled. If you want the full story you can see it at

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=191010

Steve

Chrislis SSChasing Mays Points: 622 More actions · Answer 8

I saw your topic. The script I've submmited should work for your problem too.

I'm waiting the webmasters to publish it.

noeld SSC Guru Points: 96590 More actions · Answer 9

In some cases where "Parent" tables are large it may be a good thing to drop the foreign keys to perform TRUNCATE TABLE on ALL tables (if you need to delete all the old data and make it Fast! ).

Disabling a constraint allow insert and updates but Truncate table will still see the constraint and it won't work

* Noel

steve block SSCertifiable Points: 7698 More actions · Answer 10

Chrislis,

let me know when your script is published. I'll take a look at it.

Steve