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

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






Reference:https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

List tables on their dependency order based on foreign keys for Data Migration

If you want to perform bulk insert operation on multiple tables.

The tables have foreign key relationships between themselves.

If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.



Requirement



Produce a list of tables within a database ordered according to their dependencies.

Tables with no dependencies (no foreign keys) will be 1st.

Tables with dependencies only in the 1st set of tables will be 2nd.

Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.

and so on...





WITH cte (
 lvl
 ,object_id
 ,name
 ,schema_Name
 )
AS (
 SELECT 1
  ,object_id
  ,sys.tables.name
  ,sys.schemas.name AS schema_Name
 FROM sys.tables
 INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE type_desc = 'USER_TABLE'
  AND is_ms_shipped = 0
 
 UNION ALL
 
 SELECT cte.lvl + 1
  ,t.object_id
  ,t.name
  ,S.name AS schema_Name
 FROM cte
 JOIN sys.tables AS t ON EXISTS (
   SELECT NULL
   FROM sys.foreign_keys AS fk
   WHERE fk.parent_object_id = t.object_id
    AND fk.referenced_object_id = cte.object_id
   )
 JOIN sys.schemas AS S ON t.schema_id = S.schema_id
  AND t.object_id <> cte.object_id
  AND cte.lvl < 30
 WHERE t.type_desc = 'USER_TABLE'
  AND t.is_ms_shipped = 0
 )
SELECT schema_Name
 ,name
 ,MAX(lvl) AS dependency_level
FROM cte
WHERE schema_Name LIKE '%ORCA%'
GROUP BY schema_Name
 ,name
ORDER BY dependency_level
 ,schema_Name
 ,name;



Reference

Script to drop all Foreign keys and recreate them

--Create Table and Save Foreign Keys in a Table
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

CREATE TABLE fklist (
 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 fklist (
 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 fklist
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

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

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

SELECT *
FROM fklist

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END

-----------------------------------------------------------------------------------

--Drop Foreign Keys









SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END


--Recreate Foreign Keys
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @constraint_col NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @pk_schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)
DECLARE @pk_table NVARCHAR(200)
DECLARE @pk_col NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
  ,@constraint_col = ForeignKeyConstraintColumnName
  ,@pk_schema = PrimaryKeyConstraintTableSchema
  ,@pk_table = PrimaryKeyConstraintTableName
  ,@pk_col = PrimaryKeyConstraintColumnName
 FROM fklist
 WHERE RowId = @counter

 PRINT ('ALTER TABLE [' + @schema + '].[' + @table + '] ADD CONSTRAINT ' + @constraint + ' FOREIGN KEY(' + @constraint_col + ') REFERENCES [' + @pk_schema + '].[' + @pk_table + '](' + @pk_col + ')')

 SET @counter = @counter - 1
END