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 (http://blog.sqlauthority.com)

About these ads

8 thoughts on “SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

  1. 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

  2. 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..!

  3. 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?

    • 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 ) .

  4. 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”

  5. Pingback: SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s