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 |
|
raky
Aged Yak Warrior
767 Posts |
 Posted - 2010-02-10 : 01:08:03
|
| How to truncate a table which is being referenced by a foreign key?Got the error like thisCannot truncate table 't1' because it is being referenced by a FOREIGN KEY constraint.Is there any way i can truncate. Even i tried by creating my Foreignkey with On Delete Cascade Option but iam not allowed to truncate the table t1 (master table ).Pls Advise.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-10 : 01:19:23
|
you can use DELETE FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVP |
 |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2010-02-10 : 01:24:43
|
quote:
Originally posted by visakh16
you can use DELETE FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVP
Don't feel that iam arguing with you. I too know that we can use Delete Command but I Want to Know Is it not possible to Work With Truncate in this case or not ?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-10 : 01:29:45
|
quote:
Originally posted by raky
quote:
Originally posted by visakh16
you can use DELETE FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVP
Don't feel that iam arguing with you. I too know that we can use Delete Command but I Want to Know Is it not possible to Work With Truncate in this case or not ??
its not possible when you've foreign key relation existing in table.the below is from BOLYou cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. ------------------------------------------------------------------------------------------------------SQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-10 : 01:59:09
|
| NOT RECOMMENDED: But you could DROP the Foreign Key constraint, TRUNCATE the table, and recreate the Foreign Key constraint. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-10 : 06:28:10
|
quote:
Originally posted by raky
quote:
Originally posted by visakh16
you can use DELETE FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVP
Don't feel that iam arguing with you. I too know that we can use Delete Command but I Want to Know Is it not possible to Work With Truncate in this case or not ??
Why do you want to do this?MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-10 : 07:28:18
|
| TRUNCATE = less TLog than DELETE? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-10 : 07:58:37
|
quote:
Originally posted by Kristen
TRUNCATE = less TLog than DELETE?
I was thinking that OP might want to truncate all tables where problem occurs for tables being referenced by othersMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Truncate table