Truncate Table with Foreign Key Constraint

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

  • 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

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply