Truncating a table with foreign keys

So  your in development, and you have a need to truncate a table.  You write your all-too-familiar “TRUNCATE TABLE MySchema.MyTable” But wait, you get this error message when you try and execute

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘MySchema.MyTable’ because it is being referenced by a FOREIGN KEY constraint.

What? Why? This error is telling you that your table is being referenced by another table in the database, and cannot be truncated. But what if you have already truncated the other table and you really do want to empty it?

To get around this, run a DELETE FROM command and do not put in a where clause. So your command would be:

DELETE FROM Myschema.MyTable

Now you have an empty table, but your identity column, if you have one, is going to be off. To correct it, run a dbcc command to reset it:

DBCC CHECKIDENT (‘MyTable’, RESEED, 0)

Where MyTable is the name of your table. Please note, if your table is not in the dbo schema, you will have to specify the schema name and then the table name. Like (MySchema.MyTable). And 0 is the number you want to reseed to -1 (meaning that, in this case, the next row inserted would have an identity value of 1)

Hope it helps you in your travels.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s