none
Truncate not working

    Question

  •  Hi,

     I have 5 tables out of which Table1 Id (PK) referenced to other 4 tables : Table2, Table3, Table4, Table5 as Foreign Key. 

     So i can able to truncate the 4 other tables but not Table 1 and even there is no data Table1 is not truncating..

    Error Message : Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint. So i ran delete command.. but is there any solution for this without removing constraints 

     


    Ravi




    • Edited by Tagore 534 Monday, April 30, 2018 10:21 AM
    Monday, April 30, 2018 10:19 AM

All replies

  • No, and it's a well documented restriction, see TRUNCATE TABLE (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, April 30, 2018 10:22 AM
    Moderator
  • You can try SWITCH command 

    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;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 01, 2018 5:07 AM
    Moderator