Truncate Table with Foreign Key Constraint

• clicky

  SSC Eights!

  Points: 894

  More actions

  May 25, 2017 at 8:12 am

  #337236

  Can truncate table with foreign key constraints without having to drop the keys?   I tried alter table NOCHECK CONSTRAINT ALL without avail.

• NorthernSoul

  SSCertifiable

  Points: 6869

  More actions

  May 25, 2017 at 8:18 am

  #1944175

  You can't truncate a table that has a foreign key constraint, that is the whole reason for having a constraint. You will need to delete and re-create the constraints so make sure you script them out before deleting them.

  Thanks

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  May 25, 2017 at 8:23 am

  #1944179

  No -- as you've found out.  You either have to drop the FK references, truncate and readad FKs or DELETE (if a lot of records, do in batches to not blowout your log) and reseed your identity, if needed.

  Rob

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 25, 2017 at 8:26 am

  #1944180

  The answer's yes, but only because I think you asked the wrong question.  You can truncate a table that has a foreign key constraint, but you can't truncate a table that's referenced by one.  Make sure that the referencing table is empty, then use DELETE.

  CREATE TABLE Parent (
    ParentID int PRIMARY KEY
,    Datacol varchar(10)
    );

  CREATE TABLE Child (
    ChildID int PRIMARY KEY
,    ParentID int FOREIGN KEY REFERENCES Parent(ParentID)
,    Datacol varchar(10)
    );

  INSERT INTO Parent
VALUES
    (1, 'Pig')
,    (2, 'Sheep');

  INSERT INTO Child
VALUES
    (1, 1, 'Sow')
,    (2, 1, 'Boar')
,    (3, 1, 'Piglet')
,    (4, 2, 'Ewe')
,    (5, 2, 'Ram')
,    (6, 2, 'Lamb');

  TRUNCATE TABLE Child; -- succeeds
TRUNCATE TABLE Parent; -- fails
DELETE FROM Parent; -- succeeds
  John