|
|
Error Message
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table <Table Name>' because it
is being referenced by a FOREIGN KEY constraint.
Causes
The TRUNCATE TABLE statement, similar to the DELETE statement without a WHERE clause, removes all rows from a table. But unlike the DELETE statement, the TRUNCATE TABLE statement does not log the individual row deletions thus making it faster and using fewer system and transaction log resources.
|
If the table being truncated contains an IDENTITY column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value of 1 is used.
One restriction where the TRUNCATE TABLE statement cannot be used on a table is when the table is being referenced by a FOREIGN KEY constraint. A foreign key (FK) is a column or a combination of columns that is used to establish and enforce a link between the data in two tables. A foreign key can be created by defining a FOREIGN KEY constraint when a table is created or modified.
If a table being truncated has a FOREIGN KEY constraint defined, then this error message will be raised as can be seen in the following:
CREATE TABLE [dbo].[Teacher] (
[TeacherID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
CREATE TABLE [dbo].[Subject] (
[SubjectID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[SubjectCode] VARCHAR(10),
[SubjectName] VARCHAR(100),
[TeacherID] INT REFERENCES [dbo].[Teacher] ( [TeacherID] )
)
GO
INSERT INTO [dbo].[Teacher] ( [FirstName], [LastName] )
VALUES ( 'Chuck', 'Norris' )
GO
TRUNCATE TABLE [dbo].[Teacher]
GO
Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'dbo.Teacher' because it is being referenced by a FOREIGN KEY constraint.
Even if the table does not contain any rows as long as it has a FOREIGN KEY constraint, this error message will be encountered if the TRUNCATE TABLE statement is used on the table.
Solution / Work Around:
If all rows from a table need to be deleted and the table has a FOREIGN KEY constraint defined, the DELETE statement should be used instead of the TRUNCATE TABLE statement:
DELETE FROM [dbo].[Teacher]
If one of the reasons of using the TRUNCATE TABLE is to have the IDENTITY column be reset to begin with 1 again (assuming no seed value was defined when the IDENTITY column was defined), then the IDENTITY column needs to be reset manually using the DBCC CHECKIDENT command. The DBCC CHECKIDENT command checks the current identity value for the specified table and if needed, changes the identity value. The DBCC CHECKIDENT command can also be used to manually set a new current identity value for the IDENTITY column.
The syntax of the DBCC CHECKIDENT command is as follows:
DBCC CHECKIDENT ( [, { NORESEED | { RESEED [, ] } } ] )
To reset the IDENTITY column to start with 1, the following command is issued if the table already contained data:
DBCC CHECKIDENT ( '[dbo].[Teacher]', RESEED, 0 )
If the table doesn’t have any rows, then the following command is used instead:
DBCC CHECKIDENT ( '[dbo].[Teacher]', RESEED, 1 )
|