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

,
Previous Post
SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023
Next Post
SQLAuthority News – Microsoft Whitepaper – Idle Connection Resiliency

Related Posts

15 Comments. Leave new

  • John Paul Cook
    April 11, 2014 9:23 am

    It’s important to also consider CHECK/NO CHECK when scripting foreign keys. See https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx for a similar script.

    Reply
  • 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

    Reply
  • Shubham Bansal
    May 6, 2014 12:03 pm

    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

    Reply
  • 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”.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • This doesn’t seem to actually drop and remove the constraints, but creates the commands on the fly, but doesn’t execute them.

    Reply
  • hi
    there are some foreign keys that are shown in the list of indexes , but when i want to drop them it says “‘ there is no such a foreign key constraint ‘”.

    how can it be ?

    Reply
  • Original script fails if the name of the constraint contains periods. You need to enclose name in ‘[]’.
    eg
    DROP CONSTRAINT ‘ + ForeignKeyConstraintName + ‘
    becomes
    DROP CONSTRAINT [‘ + ForeignKeyConstraintName + ‘]

    Reply
  • Vladimir Kievsky
    February 8, 2018 12:51 am

    Great script, worked well, one hitch though: the GO on the same line as delete statements had to be removed to avoid errors.

    Reply
  • Hi,

    As Sergey S points out this script does not work correctly when the FK spans more than one column, you really should update the post accordingly.

    Reply
  • SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[FetchAllForeignKeyConstraint_DROP_ALTER_DB_V001]

    AS
    SET NOCOUNT ON
    CREATE table #GETFK_Key(
    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 #GETFK_Key(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 #GETFK_Key SET
    PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
    FROM
    #GETFK_Key T
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
    ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

    UPDATE #GETFK_Key SET
    PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
    PrimaryKeyConstraintTableName = TABLE_NAME
    FROM #GETFK_Key T
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
    ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
    UPDATE #GETFK_Key SET
    PrimaryKeyConstraintColumnName = COLUMN_NAME
    FROM #GETFK_Key T
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
    ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

    With cte_duplicate (ForeignKeyConstraintTableSchema , ForeignKeyConstraintTableName , ForeignKeyConstraintName,rownumber)
    as (
    select ForeignKeyConstraintTableSchema , ForeignKeyConstraintTableName , ForeignKeyConstraintName,
    row_number()over(partition by ForeignKeyConstraintTableSchema , ForeignKeyConstraintTableName , ForeignKeyConstraintName order by ForeignKeyConstraintTableSchema , ForeignKeyConstraintTableName , ForeignKeyConstraintName)as rank
    from #GETFK_Key
    )
    DELETE from cte_duplicate where rownumber1

    SELECT

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


    FROM
    #GETFK_Key
    –ADD CONSTRAINT:
    SELECT

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

    GO’
    FROM
    #GETFK_Key

    Reply
  • Dhurandhar Singh
    June 19, 2020 11:09 am

    script is wrong , if you have primary key on more than one column i.e. primary key (id,customerid)

    Reply

Leave a Reply

Menu