In Microsoft SQL Server kun je geen truncate of drop doen op een tabel met een foreign key constraint. Neem bijvoorbeeld een klantentabel en een landentabel. Heeft de klantentabel een foreign key constraint naar de landentabel, dan kun je in deze situatie geen truncate doen op de landentabel, want countryID wordt gebruikt in een foreign key constraint.

Workaround in deze situatie:

  • Verwijder de FK, doe de truncate op de tabel en maak de FK opnieuw aan.

Het lukt niet om eerst de data uit de child-tabel (klant) te truncaten/deleten en daarna een truncate te doen op de parent-tabel (land). Je krijgt dan nog steeds de fout “Cannot truncate table ‘TableName’ because it is being referenced by a FOREIGN KEY constraint.”

Plan om dit op te lossen:

  1. Drop de constraints
  2. Doe de truncate op de tabel (of delete alles en reseed de identity)
  3. Maak de constraints opnieuw aan.

Alles in een transactie! Anders loop je het risico dingen kwijt te raken. Bedenk wel dat het verstandig is om eerst de child-tabel te truncaten of de constraint-kolom leeg te maken. Anders kun je problemen krijgen met de referentiële integriteit.

Voorbeeld

Stel dat een paar tabellen met een foreign key naar de tabel “Bedrijven” wijzen. Nu wil je een truncate doen op de tabel “Bedrijven”. Je verwijdert eerst alle constraints, doet daarna de truncate op de tabellen en voegt tot slot de constraints weer toe. Maak DROP/CREATE-scripts van alle foreign keys. Dat kan in SSMS via DROP/CREATE:

Als je alle scripts hebt, maak je het hoofdscript. Het eerste deel is de drop constraint, dan het deel met de truncate table en als laatste de create constraint:

SQL
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;

Veelgestelde vragen

Waarom krijg ik "Cannot truncate table because it is being referenced by a FOREIGN KEY constraint"?

SQL Server laat je geen truncate doen op een tabel waar een foreign key naar wijst. Dat geldt ook als de child-tabel al leeg is, want de constraint bestaat nog.

Hoe doe ik een truncate op een tabel met een foreign key in SQL Server?

Drop de foreign key constraints, doe de truncate op de tabel en maak de constraints opnieuw aan. Zet alle drie de stappen in één transactie, zodat je geen data kwijtraakt als een stap mislukt.

Kan ik eerst de child-tabel leegmaken en daarna een truncate doen op de parent-tabel?

Nee. Je krijgt dan nog steeds dezelfde fout op de parent-tabel. De foreign key moet eerst weg voordat de truncate werkt.

Hoe genereer ik de DROP- en CREATE-scripts voor alle foreign keys?

Gebruik SSMS. Dat kan voor elke foreign key een DROP en een CREATE scripten. Combineer de scripts: eerst de drops, dan de truncates en als laatste de creates.

Waarom zet ik de truncate en de wijzigingen aan de foreign keys in een transactie?

Zonder transactie loop je het risico data kwijt te raken als een stap halverwege mislukt. Het voorbeeld gebruikt BEGIN TRANSACTION met TRY en CATCH, en doet een rollback als er een fout optreedt.

Reacties

Je e-mailadres wordt niet getoond. Een foto komt van Gravatar, als je die hebt.

  1. Leeftijd berekenen in SQL Server

    SQL Server2 feb 2016 · 11,7K keer gelezen
    11,7K

  2. T-SQL - Een voortschrijdend totaal over 4 weken maken

    SQL Server7 dec 2015 · 7,4K keer gelezen
    7,4K

  3. Verloren klanten berekenen met T-SQL

    SQL Server20 aug 2014 · 5,7K keer gelezen
    5,7K
(3 rows affected)SQL Server