Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2011-09-19 : 22:01:38
|
| I am trying to clean out a table and normaly I would just do "truncate table" but this time I can't because of a FOREIGN KEY CONSTRAINT. With out droping all the relationships is ther a way to clean out the table? (No there is no other data in ANY of the tables taht relate to the one I am trying to clean out)-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 08:40:43
|
"Ok it was officialy a stupid question"maybe not  If there is a lot of data in a table then DELETE will log all the deletions, which can fill up your transaction log file and cause it to be extended. Best to avoid that happening ...You can DELETE in a loop (say 10,000 rows per iteration), and backup the transaction log frequently so that it doesn't get extended ...Or you could drop the Foreign Key constraint, Truncate the table and re-Create the Foreign Key. (This method is very skinny on the Transaction log.)The last one is probably the best way, although it involves having to create a script to Drop / Re-Create the Fkey. If you will do this often it will be worth doing - Truncate will be much faster than Delete (if more than a few rows in the table)Beware that whilst the FKey is dropped anyone adding data won't have if "checked" against the FKey ...Plenty to think about really  |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2011-09-20 : 15:00:43
|
| I have over 100 foreign key constraints on the table so droping is not really a solution. Since it is only 3 rows right now to delete I will just use the delete command.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 15:36:44
|
| "Since it is only 3 rows right now to delete I will just use the delete command"Fair enough Its easy enough to generate a script to DROP/re-CREATE them though, if you need to do it in future when there are more rows in the table. |
|
|
|
|
|
|
Truncating table with foreign key