Sql-server – Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

foreign-keyssql serversql-server-2005truncatetsql

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

Use a DELETE without a where clause and then RESEED the identity (or)
Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

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

Best Answer

Correct; you cannot truncate a table which has an FK constraint on it.

Typically my process for this is:

Drop the constraints
Trunc the table
Recreate the constraints.

(All in a transaction, of course.)

Of course, this only applies if the child has already been truncated. Otherwise I go a different route, dependent entirely on what my data looks like. (Too many variables to get into here.)

The original poster determined WHY this is the case; see this answer for more details.

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Sql – How to list all foreign keys referencing a given table in SQL Server

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner. If one exists, that table's columns are returned.

Best Answer

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

Sql – How to list all foreign keys referencing a given table in SQL Server

Related Topic