how to truncate the primary key table if we have foreign key constraint for a column in another table?

sql-server-2008, sql-server-2008-r2

Solution

The only way to allow a truncate is to drop the foreign key constraint(s) against table A. It doesn't matter if the constraint is disabled or if both tables are empty, SQL Server still will not allow it. So if you have the definition of the foreign key handy, you could do:

ALTER TABLE dbo.TableB DROP CONSTRAINT FK_whatever;

TRUNCATE TABLE dbo.TableA;

ALTER TABLE dbo.TableB ADD CONSTRAINT FK_Whatever
  FOREIGN KEY ...;

Otherwise as @Damien says the solution to your "genuine problem" is to use `DELETE` instead of `TRUNCATE`. If you also were using `TRUNCATE` to reset the `IDENTITY` column, you can perform a `DELETE` and then a `DBCC CHECKIDENT('dbo.TableA', RESEED, 1);`...

Problem

Hi i am using sql server 2008 r2, i have a genuine problem. i have a Table A and Table B, where i have a column IID in table A as a primary key constraint. and the same column i.e. IID in table B as a foreign key constraint. i have a situation where i wanted to truncate the table A. while running query Truncate table A it give me following error. ``` Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'A' because it is being referenced by a FOREIGN KEY constraint. ``` my problem i cant do any DML & DDL operation on table B. how can i truncate table A ? Thanks! in advanced.

Original source