solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community to collaborate to quickly resolve problems, perform maintenance and capture best practices.

Learn more!

Disable, enable, drop and recreate SQL Server Foreign Keys

By: Greg Robidoux | Read Comments (7) | Print

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More

Problem
Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?

Solution
As mentioned already other tips have been written that show you how to find your foreign keys within your database and why you should use foreign keys. You can refer to these tips for this information:

Below is a script that can be used to find all foreign keys that reference the primary table that you wish to work with. In this script you provide the table name and the schema name (object owner). The script will then return a list of statements that can be copied and pasted into a query window to make these changes.

The script also takes three different parameter values depending on the action you want to take:

DISABLE - this will create the command to disable all FK constraints that reference the table you are working with
ENABLE - this will create the command to enable all FK constraints that reference the table you are working with
DROP - this will create a command to drop all FK constraints and create a command to create all FK constraints that reference the table are working with

The values below use a table in the AdventureWorks database, so you can just copy and paste this code and run this sample against that database.

-- Enable, Disable, Drop and Recreate FKs based on Primary Key table  
-- Written 2007-11-18  
-- Edgewood Solutions / MSSQLTips.com  
-- Works for SQL Server 2005  

SET NOCOUNT ON  

DECLARE @operation VARCHAR(10)  
DECLARE @tableName sysname  
DECLARE @schemaName sysname  

SET @operation = 'DROP' --ENABLE, DISABLE, DROP  
SET @tableName = 'SpecialOfferProduct'  
SET @schemaName = 'Sales'  

DECLARE @cmd NVARCHAR(1000) 

DECLARE   
   @FK_NAME sysname,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME sysname,  
   @FKTABLE_OWNER sysname,  
   @PKTABLE_NAME sysname,  
   @PKTABLE_OWNER sysname,  
   @FKCOLUMN_NAME sysname,  
   @PKCOLUMN_NAME sysname,  
   @CONSTRAINT_COLID INT  


DECLARE cursor_fkeys CURSOR FOR   
   SELECT  Fk.name,  
           Fk.OBJECT_ID,   
           Fk.is_disabled,   
           Fk.is_not_for_replication,   
           Fk.delete_referential_action,   
           Fk.update_referential_action,   
           OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,   
           schema_name(Fk.schema_id) AS Fk_table_schema,   
           TbR.name AS Pk_table_name,   
           schema_name(TbR.schema_id) Pk_table_schema  
   FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
           sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   
   WHERE   TbR.name = @tableName  
           AND schema_name(TbR.schema_id) = @schemaName  

OPEN cursor_fkeys  

FETCH NEXT FROM   cursor_fkeys   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER  

WHILE @@FETCH_STATUS = 0   
BEGIN   

   -- create statement for enabling FK  
   IF @operation = 'ENABLE'   
   BEGIN  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
           + ']  CHECK CONSTRAINT [' + @FK_NAME + ']'  

      PRINT @cmd  
   END  

   -- create statement for disabling FK  
   IF @operation = 'DISABLE'  
   BEGIN     
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
           + ']  NOCHECK CONSTRAINT [' + @FK_NAME + ']'  

      PRINT @cmd  
   END  

   -- create statement for dropping FK and also for recreating FK  
   IF @operation = 'DROP'  
   BEGIN  

       -- drop statement  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + ']'     

      PRINT @cmd  

       -- create process  
       DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT  

       -- create cursor to get FK columns  
       DECLARE cursor_fkeyCols CURSOR FOR   
       SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,   
               COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name  
       FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
               sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN   
               sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID   
       WHERE   TbR.name = @tableName  
               AND schema_name(TbR.schema_id) = @schemaName

AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008


       ORDER BY Fk_Cl.constraint_column_id  

       OPEN cursor_fkeyCols  

       FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  

       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  
         
       WHILE @@FETCH_STATUS = 0   
       BEGIN   

           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  

           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  

           SET @COUNTER = @COUNTER + 1  
             
           FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  
       END  

       CLOSE cursor_fkeyCols   
       DEALLOCATE cursor_fkeyCols   

       -- generate create FK statement  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
           CASE @FK_DISABLED   
               WHEN 0 THEN ' CHECK '  
               WHEN 1 THEN ' NOCHECK '  
           END +  ' ADD CONSTRAINT [' + @FK_NAME   
           + '] FOREIGN KEY (' + @FKCOLUMNS   
           + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
           + @PKCOLUMNS + ') ON UPDATE ' +   
           CASE @UPDATE_RULE   
               WHEN 0 THEN ' NO ACTION '  
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET_NULL '   
               END + ' ON DELETE ' +   
           CASE @DELETE_RULE  
               WHEN 0 THEN ' NO ACTION '   
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET_NULL '   
               END + '' +  
           CASE @FK_NOT_FOR_REPLICATION  
               WHEN 0 THEN ''  
               WHEN 1 THEN ' NOT FOR REPLICATION '  
           END  

      PRINT @cmd  

   END  

   FETCH NEXT FROM    cursor_fkeys   
      INTO @FK_NAME,@FK_OBJECTID,  
           @FK_DISABLED,  
           @FK_NOT_FOR_REPLICATION,  
           @DELETE_RULE,     
           @UPDATE_RULE,     
           @FKTABLE_NAME,  
           @FKTABLE_OWNER,  
           @PKTABLE_NAME,  
           @PKTABLE_OWNER  
END  

CLOSE cursor_fkeys   
DEALLOCATE cursor_fkeys

Running the script

Here are a few sample outputs that you will get running this against the AdventureWorks database.

Example 1:

Table: SpecialOfferProduct
Schema: Sales
Operation: DROP

ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]

ALTER TABLE [Sales].[SalesOrderDetail] WITH NOCHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY ([SpecialOfferID],[ProductID]) REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID]) ON UPDATE NO ACTION ON DELETE NO ACTION

Example 2:

Table: SpecialOfferProduct
Schema: Sales
Operation: DISABLE

ALTER TABLE [Sales].[Individual] NOCHECK CONSTRAINT [FK_Individual_Contact_ContactID]

ALTER TABLE [Sales].[SalesOrderHeader] NOCHECK CONSTRAINT [FK_SalesOrderHeader_Contact_ContactID]

ALTER TABLE [Sales].[StoreContact] NOCHECK CONSTRAINT [FK_StoreContact_Contact_ContactID]

ALTER TABLE [Purchasing].[VendorContact] NOCHECK CONSTRAINT [FK_VendorContact_Contact_ContactID]

ALTER TABLE [Sales].[ContactCreditCard] NOCHECK CONSTRAINT [FK_ContactCreditCard_Contact_ContactID]

ALTER TABLE [HumanResources].[Employee] NOCHECK CONSTRAINT [FK_Employee_Contact_ContactID]

Next Steps

To make this script more useful and to have this available when needed convert this to a stored procedure
This tip has been updated thanks to input from John (jtgooding)

Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More | Become a paid author

Last Update: 11/19/2007

Share:

Connect with MSSQLTips.com

Comments and Feedback:

Thursday, March 13, 2008 - 5:34:15 AM - jtgooding

Read The Tip

Nice script it has 1 small bug, the inner cursor select is missing a predicate statement to limit the keys returned to be only for the current fk of the outer cursor.

SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
             COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk
      LEFT OUTER JOIN sys.tables TbR
              ON TbR.OBJECT_ID = Fk.referenced_object_id
     INNER JOIN sys.foreign_key_columns Fk_Cl
              ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @tableName
       AND schema_name(TbR.schema_id) = @schemaName
      AND fk.OBJECT_ID = @FK_ObjectID                     -- this line needs to be here
ORDER BY Fk_Cl.constraint_column_id

I will be testing it out today in more depth to see if anything else is missing.

John

Wednesday, June 11, 2008 - 8:04:07 AM - kosmas

Read The Tip

a second small bug is that it uses the FK.is_disabled in order to set the "Check Existing Data On Creation or Re-enabling" attribute in the creation of the FK constraint (ALTER TABLE <mytable> <NOCHECK or CHECK> ADD CONSTRAINT <myconstraint> ... ).

The FK.is_disabled holds the "Enforce Foreign Key Constraint" attribute and it should add a second alter for FKs with FK.is_disabled=0 that disables the FK (ALTER TABLE <mytable> NOCHECK CONSTRAINT <myconstraint>).

The question now is How we can find the "Check Existing Data On Creation or Re-enabling" attribute from SQL server metadata in order to set properly the above flag ?

Thursday, June 12, 2008 - 12:20:15 PM - admin

Read The Tip

I have updated the script based on the missing line you pointed out. This was a good catch.

Thanks

Wednesday, August 05, 2009 - 2:59:59 PM - scolvard

Read The Tip

You could also create yet another outer cursor to check all tables instead of just 1 at a time (Similar for schema name, etc.) if you really wanted to:

DECLARE @PKTABLE_NAME_Real sysname

DECLARE cursor_tblfind CURSOR FOR

SELECT Distinct TbR.name AS Pk_table_name

FROM sys.foreign_keys Fk LEFT OUTER JOIN

sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id

Order By TbR.name

OPEN cursor_tblfind

FETCH NEXT FROM cursor_tblfind

INTO @PKTABLE_NAME_Real

WHILE @@FETCH_STATUS = 0

BEGIN

SET @operation = 'DROP' --ENABLE, DISABLE, DROP

SET @tableName = @PKTABLE_NAME_Real

SET @schemaName = 'dbo'

DECLARE @tblname NVARCHAR (30)

DECLARE @cmd NVARCHAR(1000) ........etc.etc.

Then at the End do something like:

CLOSE cursor_fkeys

DEALLOCATE cursor_fkeys

FETCH NEXT FROM cursor_tblfind

INTO @PKTABLE_NAME_Real

END

CLOSE cursor_tblfind

DEALLOCATE cursor_tblfind

Sunday, February 26, 2012 - 5:20:23 AM - Amir Hussein Samiani

Read The Tip

--Drop and Foreign Key Constraints

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

Sunday, February 26, 2012 - 5:28:30 AM - Amir Hussein Samiani

Read The Tip

--Drop and Recreate Foreign Key Constraints

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 + ']

GO'

FROM

@table

Sunday, February 26, 2012 - 11:35:03 AM - Amir Hussein Samiani

Read The Tip

--Drop and Recreate Foreign Key Constraints

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 + ']

GO'

FROM

@table

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

Write, edit, and explore SQL effortlessly with SQL Prompt.

Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST