SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

Here is an email I received during the weekend.

“Hi Pinal,

I am a senior tester in the leading organization and we have two different environments 1) Testing 2) Production.

As a part of the testing we want to insert garbage data into the database system and see how the application behaves in this scenario. However, there is a small problem. Everytime when I try to insert garbage data in the database system the tables start giving me error that due to constraints on the table, I need to populate data in certain order and it has to be correct. Actually, we want to populate the bad data and see how application reacts to it. Upon talking to development team regarding this they suggested that we should skip this test as due to contraints there will be never bad data. Though, I understand their viewpoint, I must document how the application behaves when there are bad data and data integrity is compromised.

Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”

This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.

There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – Download PowerPivot or PowerView enabled Workbook Optimizer – Download SQL Server Connector for Apache Hadoop
Next Post
SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

Related Posts

18 Comments. Leave new

  • There is a script on TechNet for generating drop and create statements for foreign keys. https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

    Reply
  • Swastik Mishra
    April 29, 2013 4:12 pm

    The below script can be used to drop and recreate the Foreign Key Constraints on a database.

    In this script we are using a temporary tables to select the existing foreign keys and the respective column name and table name. Then we determine the primary key table and column name and accordingly drop and recreate them. I am sure there would be many other ways to do this too, but this script would do it in one instance.

    SET NOCOUNT ON

    DECLARE @table TABLE(
    RowId INT PRIMARY KEY IDENTITY(1, 1),
    ForeignKeyConstraintName NVARCHAR(200),
    ForeignKeyConstraintTableSchema NVARCHAR(200),
    ForeignKeyConstraintTableName NVARCHAR(200),
    ForeignKeyConstraintColumnName NVARCHAR(200),
    PrimaryKeyConstraintName NVARCHAR(200),
    PrimaryKeyConstraintTableSchema NVARCHAR(200),
    PrimaryKeyConstraintTableName NVARCHAR(200),
    PrimaryKeyConstraintColumnName NVARCHAR(200)
    )

    INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
    SELECT
    U.CONSTRAINT_NAME,
    U.TABLE_SCHEMA,
    U.TABLE_NAME,
    U.COLUMN_NAME
    FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
    ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    WHERE
    C.CONSTRAINT_TYPE = ‘FOREIGN KEY’

    UPDATE @table SET
    PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
    FROM
    @table T
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
    ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

    UPDATE @table SET
    PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
    PrimaryKeyConstraintTableName = TABLE_NAME
    FROM @table T
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
    ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

    UPDATE @table SET
    PrimaryKeyConstraintColumnName = COLUMN_NAME
    FROM @table T
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
    ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

    –SELECT * FROM @table

    –DROP CONSTRAINT:
    SELECT

    ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
    DROP CONSTRAINT ‘ + ForeignKeyConstraintName + ‘

    GO’
    FROM
    @table

    –ADD CONSTRAINT:
    SELECT

    ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
    ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ + ForeignKeyConstraintColumnName + ‘) REFERENCES [‘ + PrimaryKeyConstraintTableSchema + ‘].[‘ + PrimaryKeyConstraintTableName + ‘](‘ + PrimaryKeyConstraintColumnName + ‘)

    GO’
    FROM
    @table

    GO

    Reply
  • Thank you

    Reply
  • Hi Pinal,

    I am suffering from 2months with this problem
    Requirement: copying database (without data) and disabling all constraints,after importing the data ,enable the constraints.
    how to disable all constraints and how to enable all constraints
    couild you help me..!

    Reply
  • Hello Pinal,
    Thanks for the post and when I execute those statements in my test server I am getting the following error ..The ALTER TABLE statement conflicted with the FOREIGN KEY constraint..may I know the reason?

    Reply
    • Ankur Agarwal
      April 11, 2014 4:24 pm

      It seems that Some foreign key constraint is failing while enabling it… You can check for this by
      select * from where not in (select from ) .

      Reply
  • Hello Pinal ,
    The above code for enabling the constraints does not work for me.This works for me
    EXEC sp_msforeachtable “ALTER TABLE ? CHECK CONSTRAINT all”

    Reply
  • Didn’t worked for me when I executed the script and trying to insert data in to the table it is still rasing me an error

    Reply
  • This seems to be my new headache, I simply want to import table data (thru MS Import and Export wizard )blocking the constraints before the process and enable then later.

    Reply
  • Hello Pinal,

    I follow your blog regularly and love all your posts.
    I am facing a problem and I can’t find any solution…
    My client asked me to create 2 stored procedures – one that drops all the constraints(PK, FK, default values) from the database and one that re-creates them (for each table).

    Here is how I create a constraint:

    ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [DF_TableName_CreateDate] DEFAULT (getdate()) FOR [CreateDate]

    Here is how I drop a constraint:

    SELECT @SQL += N’
    ALTER TABLE ‘ + OBJECT_NAME(PARENT_OBJECT_ID) + ‘ DROP CONSTRAINT ‘ + OBJECT_NAME(OBJECT_ID) + ‘;’
    FROM SYS.OBJECTS
    WHERE TYPE_DESC LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(PARENT_OBJECT_ID) = ‘TableName’;

    When we execute the stored procedures and run the website at the same time we are getting the following error:
    Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Is there a way to create / drop these constraints without locking the tables?
    Any help would be appreciated.

    Reply
  • thanks so much for this working solution

    Reply
  • Thank you for the article and the scripts. It helped me to improve performance while I delete data from big database. But I can not understand why a delete works better when foreign keys are disabled. Who can explain it for me?

    Reply
    • because it has to validate data from FK tables. Query plan would give you your answer.

      Reply
      • Thank you for the answer. This is weird for me because I disable constraints of table on which the table depends. I am trying to simplify my case to this example:

        create table Grand (grand_id int primary key)

        create table Parent (parent_id int primary key, parent_grand_id int)
        alter table Parent add constraint FK_Parent_parent_grand_id FOREIGN KEY(parent_grand_id) REFERENCES Grand(grand_id)

        create table Child (child_id int primary key, child_parent_id int)
        alter table Child add constraint FK_Child_parent_id_Child FOREIGN KEY(child_parent_id) REFERENCES Parent(parent_id)

        insert into Grand values (1)
        insert into Parent values (1, 1), (2, 1)

        ALTER TABLE Parent NOCHECK CONSTRAINT ALL
        delete from Parent
        ALTER TABLE Parent WITH CHECK CHECK CONSTRAINT ALL

        insert into Parent values (1, 1), (2, 1)
        delete from Parent

        As you can see I don’t disable constraints of Child table

  • Dear Pinal,
    What about “ON DELETE CASCADE” syntax?
    The proposed script does not take such options into account and could lead the innocent accidental dba to an irreversible situation…
    Please for your comment.
    Regards,
    Konstantinos

    Reply

Leave a Reply

Menu