Home / T-SQL / Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

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

Using Microsoft SQL Server, you cannot truncate or drop a table with a foreign key constraint. For example, when you have a client table and a country table. When the client table has a foreign key constraint to the country table, you cannot truncate the country table in this situation  because the countryID is in use a a foreign key constraint.

Workaround in this situation:

  • Remove the FK, truncate the table, and recreate the FK.

It’s not possible to truncate/delete data from the child table first (client) and then truncate the parent table (country), you will still receive the error “Cannot truncate table ‘TableName’ because it is being referenced by a FOREIGN KEY constraint.”

Plan to fix this:

  1. Drop the constraints
  2. Truncate the table (or delete everything and reseed identity)
  3. Recreate the constraints.

All in a transaction! Otherwise you are facing the risk of losing stuff. Keep in mind that it’s a good idea to truncate the child first or empty the constraint column, otherwise you can get problems with referential integrity.

Example

Imagine you have some tables pointing to the “Bedrijven” table with a foreign key. Now you want to truncate the “Bedrijven” table. You should first remove all constraints, then truncate the tables and after all add the constraints. Create DROP/CREATE scripts of all foreign keys. This can be done via SSMS via DROP/CREATE:

After you have created all scripts, create the main script. First part is the drop constraint, then the truncate table part and last is the create constraint:

BEGIN TRANSACTION;
 
BEGIN TRY
	ALTER TABLE [dbo].[Relatie_Bedrijven] DROP CONSTRAINT [FK_Relatie_Bedrijven_Bedrijven];
	ALTER TABLE [dbo].[Debiteur_Bedrijven] DROP CONSTRAINT [FK_Debiteur_Bedrijven_Bedrijven];
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent] DROP CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven];
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent] DROP CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven_Parent]
 
	TRUNCATE TABLE dbo.Bedrijven_Bedrijven_Parent
	TRUNCATE TABLE dbo.Debiteur_Bedrijven
	TRUNCATE TABLE dbo.Relatie_Bedrijven
	TRUNCATE TABLE dbo.Bedrijven
 
	ALTER TABLE [dbo].[Relatie_Bedrijven]  WITH CHECK ADD  CONSTRAINT [FK_Relatie_Bedrijven_Bedrijven] FOREIGN KEY([BedrijvenID])
	REFERENCES [dbo].[Bedrijven] ([BedrijvenID])
	ALTER TABLE [dbo].[Relatie_Bedrijven] CHECK CONSTRAINT [FK_Relatie_Bedrijven_Bedrijven]
	ALTER TABLE [dbo].[Debiteur_Bedrijven]  WITH CHECK ADD  CONSTRAINT [FK_Debiteur_Bedrijven_Bedrijven] FOREIGN KEY([BedrijvenID])
	REFERENCES [dbo].[Bedrijven] ([BedrijvenID])
	ALTER TABLE [dbo].[Debiteur_Bedrijven] CHECK CONSTRAINT [FK_Debiteur_Bedrijven_Bedrijven]
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent]  WITH CHECK ADD  CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven] FOREIGN KEY([BedrijvenID])
	REFERENCES [dbo].[Bedrijven] ([BedrijvenID])
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent] CHECK CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven]
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent]  WITH CHECK ADD  CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven_Parent] FOREIGN KEY([ParentBedrijvenID])
	REFERENCES [dbo].[Bedrijven] ([BedrijvenID])
	ALTER TABLE [dbo].[Bedrijven_Bedrijven_Parent] CHECK CONSTRAINT [FK_Bedrijven_Bedrijven_Parent_Bedrijven_Parent]
 
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
 
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
 
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Check Also

Postcoderange opsplitsen naar losse records

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *