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)
15 Comments. Leave new
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.
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
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
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”.
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
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?
This doesn’t seem to actually drop and remove the constraints, but creates the commands on the fly, but doesn’t execute them.
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 ?
can you find out what they are? It could be something wrong with the script.
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 + ‘]
Great script, worked well, one hitch though: the GO on the same line as delete statements had to be removed to avoid errors.
Will try to fix it.
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.
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
script is wrong , if you have primary key on more than one column i.e. primary key (id,customerid)