May 25, 2017 at 8:12 am
Can truncate table with foreign key constraints without having to drop the keys? I tried alter table NOCHECK CONSTRAINT ALL without avail.
May 25, 2017 at 8:18 am
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
May 25, 2017 at 8:23 am
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
May 25, 2017 at 8:26 am
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