Home Dashboard Directory Help
Search

Truncate Table when FK are disable by Rafael Salas


Status: 

Closed
 as Won't Fix Help for as Won't Fix


23
0
Sign in
to vote
Type: Suggestion
ID: 251876
Opened: 1/15/2007 9:06:18 AM
Access Restriction: Public
1
Workaround(s)
view

Description

Performing a Truncate table is sometimes necessary even when there are foreign key constraints that point to the table to-be-truncated. Disabling FKs should be enough to accomplish that. Actually, this is something that can be done in other DB engines (Oracle). Having to Drop/Create constraints or use DELETE statements are work around that either involve more steps or create unnecessary overhead in the system.
Details
Sign in to post a comment.
Posted by Paul White NZ on 5/11/2011 at 5:07 PM
Added a workaround that lets you use TRUNCATE without dropping and recreating the FK constraints.
Posted by Microsoft on 5/24/2007 at 3:34 PM
Hello

Thank you for your feedback. Though there is a simple workaround for your request, allowing TRUNCATE TABLE when the referencing foreign key constraint has been disabled would be much faster than using the DELETE statement. We will take your request into consideration for a future release of SQL Server.

Thanks once again!

-- SQL Server Team
Sign in to post a workaround.
Posted by Paul White NZ on 5/11/2011 at 5:06 PM
This allows TRUNCATE without dropping constraints:

USE tempdb;
GO
DROP TABLE
    dbo.Child1,
    dbo.Child2,
    dbo.Parent;
GO
-- Test tables
CREATE TABLE dbo.Parent (parent_id INT PRIMARY KEY);
CREATE TABLE dbo.Child1 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C1_P REFERENCES dbo.Parent);
CREATE TABLE dbo.Child2 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C2_P REFERENCES dbo.Parent);
GO
INSERT dbo.Parent VALUES (1), (2), (3);
INSERT dbo.Child1 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
INSERT dbo.Child2 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
GO
-- Error 4712
TRUNCATE TABLE dbo.Parent;

-- Disable constraint checking on the referecing tables
ALTER TABLE dbo.Child1 NOCHECK CONSTRAINT FK_C1_P;
ALTER TABLE dbo.Child2 NOCHECK CONSTRAINT FK_C2_P;

-- This would still fail
-- TRUNCATE TABLE dbo.Parent;

-- Can switch, truncate, and switch back
CREATE TABLE dbo.Workspace (parent_id INT PRIMARY KEY);
ALTER TABLE dbo.Parent SWITCH TO dbo.Workspace;
TRUNCATE TABLE dbo.Workspace;
ALTER TABLE dbo.Workspace SWITCH TO dbo.Parent;

-- Remove child rows
TRUNCATE TABLE dbo.Child1;
TRUNCATE TABLE dbo.Child2;

-- Success
ALTER TABLE dbo.Child1
    WITH CHECK
    CHECK CONSTRAINT FK_C1_P;

ALTER TABLE dbo.Child2
    WITH CHECK
    CHECK CONSTRAINT FK_C2_P;