Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to truncate a table being referenced by a FOREIGN KEY constraint ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 525
    Comment on it

    Many times we need to truncate a table which has an FK constraint on it. Typically we get the following error:

    Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

    The solution to the above problem is to follow the below steps :

    1) Drop the constraints

    2) Truncate the table

    3) Recreate the constraints.

    Of course, the above will only work provided the child has already been truncated.

    Alternatively you can Use a DELETE without a where clause and then RESEED the identity as below:

    DELETE FROM tablename
    
    DBCC CHECKIDENT ('tablename', RESEED, 0)
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: