June 4, 2007 at 9:25 am
In my database, I have a lot of test data that I want to get rid of before I take the database live.
Since there are relationships set up between the tables, I am unable to truncate the tables. I get the error:
Cannot truncate table 'table_name' because it is being referenced by a FOREIGN KEY constraint.
What is the best way of dealing with this? I can remove the relationships one by one, but it will be tedious to recreate them.
Thank you for your assistance.
Norbert
June 4, 2007 at 9:36 am
Since the relationships are there for a reason you should respect them and delete the subordinate data first. These relationships prevent you from orphaning data in subordinate tables which would leave your data much more messy then if you had just left the test data in. If you are removing all data from the database then it might be easier to drop the database and recreate it (it's a god test of your creation scripts 🙂
James.
June 4, 2007 at 9:53 am
James - thank you for the reply.
1. I tried truncating all the supporting tables, but for some reason, i am still getting the same error for the main table that has all the foreign keys pointing to.
2. I do need to keep data in some of the tables, so dropping and recreating the complete database is not an option.
Norbert
June 4, 2007 at 10:15 am
If a table is referenced by a subordinate table you can not use the truncate table command even if the subordinate tables are empty. You will have to use a regular delete command. If the table is "huge", several million records then you should break the delete command up, either use a descriminating where clause or "set rowcount 100000" which would delete only 100,000 records MAX each time (don't forget to turn it back off "set rowcount 0").
James.
June 4, 2007 at 11:01 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply