Disable foreign key constraint on all tables didn't work

Question

I try a lot of commands to disable tables constraints in my database to make truncate to all tables but still now it give me the same error:

Cannot truncate table '' because it is being referenced by a FOREIGN KEY constraint.

I try

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSforeachtable "TRUNCATE TABLE ?"

And I tried this for each table

ALTER TABLE [Table Name] NOCHECK CONSTRAINT ALL  
truncate table [Table Name]
ALTER TABLE [Table Name] CHECK CONSTRAINT ALL

and every time I have the previous error message. How can I solve this problem?

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

If you want to truncate the table, probably you have to drop the foreign keys and add them back. From here.

"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger."

Similar question here in SO.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 20 '10 at 07:54

Ashish Gupta

14,869
20
75
134

1

Indeed. TRUNCATE requires DROP. – gbn Mar 20 '10 at 12:14
From the first link: "You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; **instead, use DELETE statement without a WHERE clause**." (Emphasis mine.) – ladenedge Mar 22 '11 at 18:44

score 1 · Answer 2 · edited Jul 03 '19 at 19:05

I think is a rather bad idea. It isn't a good idea to blindly run those sort of SQL commands on ALL tables in your database.

What exactly are you trying to do?

If you want to create a copy of your Database (for example) with the same tables, with no data in them you can do that with SSIS package very easily (just make sure the COPY DATA) option is set to False

http://www.kodyaz.com/articles/transfer-sql-server-objects-task.aspx

Disable foreign key constraint on all tables didn't work

2 Answers2