SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

Earlier I wrote a blog post about how to Disable and Enable all the Foreign Key Constraint in the Database. It is a very popular article. However, there are some scenarios when user needs to drop and recreate the foreign constraints. Here is a fantastic blog comment by SQL Expert Swastik Mishra. He has written a script which drops all the foreign key constraints and recreates them. He uses temporary tables to select the existing foreign keys and respective column name and table name. Then determine the primary key table and column name and accordingly drop and recreate them.

Here is a script by Swastik and it works great.

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

Thanks Swastik for a great script. Swastik, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

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

About these ads

6 thoughts on “SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

  1. Note that the above script does not work correctly if FK created on several columns. So, I suggest is my alternative:

    SELECT ‘
    ALTER TABLE ‘ + t.parent_name + ‘ DROP CONSTRAINT [‘ + t.name + ‘];
    GO;
    ALTER TABLE ‘ + t.parent_name + ‘
    ADD CONSTRAINT [‘ + t.name + ‘] FOREIGN KEY(‘
    + STUFF((
    SELECT ‘, ‘ + COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
    FROM sys.foreign_key_columns fkc
    WHERE fkc.constraint_object_id = t.[object_id]
    FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(1024)’), 1, 2, ”) + ‘)’ + ‘
    REFERENCES [‘ + OBJECT_SCHEMA_NAME(t.referenced_object_id) + ‘].[‘ + OBJECT_NAME(t.referenced_object_id) + ‘](‘
    + STUFF((
    SELECT ‘, ‘ + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
    FROM sys.foreign_key_columns fkc
    WHERE fkc.constraint_object_id = t.[object_id]
    FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(1024)’), 1, 2, ”) + ‘)
    GO;’
    FROM (
    SELECT
    fk.name
    , fk.[object_id]
    , parent_name = ‘[‘ + SCHEMA_NAME(fk.[schema_id]) + ‘].[‘ + OBJECT_NAME(fk.parent_object_id) + ‘]’
    , fk.referenced_object_id
    FROM sys.foreign_keys fk
    ) t

  2. Hi Pinal,

    Hope you are doing good.

    I need to ask that is there any way to re-create all the foreign key constraints on all the tables at once into TFS.

    Waiting for your reply.

    Regards,
    Shubham

  3. When I run the add constraint section, I get the following error.
    Msg 1087, Level 15, State 2, Line 9
    Must declare the table variable “@table”.

  4. I had to preserve the on cascade settings so I modified Sergey’s script as follows:

    SELECT ‘ ALTER TABLE ‘ + t.parent_name + ‘ DROP CONSTRAINT [‘ + t.name + ‘] ; ‘
    as DropConstraint,

    ‘ ALTER TABLE ‘ + t.parent_name + ‘
    ADD CONSTRAINT [‘ + t.name + ‘] FOREIGN KEY(‘
    + STUFF((
    SELECT ‘, ‘ + COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
    FROM sys.foreign_key_columns fkc
    WHERE fkc.constraint_object_id = t.[object_id]
    FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(1024)’), 1, 2, ”) + ‘)’ + ‘
    REFERENCES [‘ + OBJECT_SCHEMA_NAME(t.referenced_object_id) + ‘].[‘ + OBJECT_NAME(t.referenced_object_id) + ‘](‘
    + STUFF((
    SELECT ‘, ‘ + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
    FROM sys.foreign_key_columns fkc
    WHERE fkc.constraint_object_id = t.[object_id]
    FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(1024)’), 1, 2, ”) + ‘) ‘
    + t.OnDelete COLLATE DATABASE_DEFAULT
    + ‘ ‘
    + t.OnUpdate COLLATE DATABASE_DEFAULT
    + ‘ ; ‘
    as CreateConstraint
    into ##ForeignKeys
    FROM (
    SELECT
    fk.*
    , ‘[‘ + SCHEMA_NAME(fk.[schema_id]) + ‘].[‘ + OBJECT_NAME(fk.parent_object_id) + ‘]’ as Parent_Name
    , ‘ ‘ + CASE WHEN DELETE_REFERENTIAL_ACTION = 1 THEN ‘ ON DELETE ‘+ DELETE_REFERENTIAL_ACTION_DESC else ‘ ‘ END as OnDelete
    , ‘ ‘ + CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN ‘ ON UPDATE ‘+ UPDATE_REFERENTIAL_ACTION_DESC else ‘ ‘ END as OnUpdate
    FROM sys.foreign_keys fk
    ) t

    SELECT * FROM ##ForeignKeys

    drop table ##ForeignKeys

  5. Hi,
    I’d like to remove repetitive Foreign key constraints., e.g., a table can have different constraint for the same column with same primary key table only the difference would be constraint name.
    is it possible?

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