SQLBlog Nederland Business Intelligence & Datawarehousing
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:
- Drop the constraints
- Truncate the table (or delete everything and reseed identity)
- 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; |
