Categories
Databases

Truncating a Database Table with a Foreign Key

To remove all data from a table and reset its primary key you can normally run the following SQL command: TRUNCATE TABLE MyTable; However, if you try this on a table with one or more foreign keys, you’re going to get an error message. Normally, the way around this is to drop the constraints, truncate […]

To remove all data from a table and reset its primary key you can normally run the following SQL command:

TRUNCATE TABLE MyTable;

However, if you try this on a table with one or more foreign keys, you’re going to get an error message. Normally, the way around this is to drop the constraints, truncate the table, and recreate the constraints – which is a lot of fuss.

The following does the same job, but without the fuss.

DELETE FROM MyTable;
DBCC CHECKIDENT (MyTable, RESEED, 0);