|Article Time Stamp: 18 February 2009, 23:42:14 GMT+7|
Microsoft SQL (MSSQL): How To Truncate Table That Being Referenced By Another Table
Let say you have 2 table:
1. Table User
2. Table Access_Privilege
At Access_Privilege table, you create a Foreign Key to User table. And you have no records at Access_Privilege table, but you have 10 records at User table.
Then, when you want to truncate User table you got an error:
Cannot truncate table User, because it is being referenced by a FOREIGN KEY constraint.
In order to truncate User table, you must do these steps:
1. Alter Access_Privilege table to drop the Foreign Key constraint
2. Truncate the User table
3. Alter Access_Privilege table to re-create the Foreign Key constraint
FYI, you can use sp help to find the Foreign Key constraint, by using this syntax: