solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Disable, enable, drop and recreate SQL Server Foreign Keys

MSSQLTips author Greg Robidoux By:   |   Read Comments (21)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Constraints

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_idAS Fk_table_name,  
           
schema_name(Fk.schema_idAS Fk_table_schema,  
           
TbR.name AS Pk_table_name,  
           
schema_name(TbR.schema_idPk_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_idFk_Cl.parent_column_idAS Fk_col_name,  
               
COL_NAME(Fk.referenced_object_idFk_Cl.referenced_column_idAS 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 
       
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 
            
           
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 THEN ' CHECK ' 
               
WHEN THEN ' NOCHECK ' 
           
END +  ' ADD CONSTRAINT [' @FK_NAME  
           
'] FOREIGN KEY (' @FKCOLUMNS  
           
') REFERENCES [' @PKTABLE_OWNER '].[' @PKTABLE_NAME '] ('  
           
@PKCOLUMNS ') ON UPDATE ' +  
           
CASE @UPDATE_RULE  
               
WHEN THEN ' NO ACTION ' 
               
WHEN THEN ' CASCADE '  
               
WHEN THEN ' SET_NULL '  
               
END ' ON DELETE ' +  
           
CASE @DELETE_RULE 
               
WHEN THEN ' NO ACTION '  
               
WHEN THEN ' CASCADE '  
               
WHEN THEN ' SET_NULL '  
               
END '' 
           
CASE @FK_NOT_FOR_REPLICATION 
               
WHEN THEN '' 
               
WHEN 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)


Last Update: 11/19/2007


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



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

 

GO


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

  ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

        

  GO'

    FROM

  @table

 

    GO


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

    ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   

    GO'

FROM

    @table

 

GO


Tuesday, July 17, 2012 - 9:16:46 AM - Efthakhar Read The Tip
The ability to think like that shows you're an exerpt

Sunday, October 14, 2012 - 11:16:31 PM - Darrin Wolf Read The Tip
-- Okay, sorry, better font here (duplicate of last post).
-- The last one in this list is problematic at best; deletes the constraints, and errors out trying to put them back!
-- Folks, all you want to probably do is 'disable' (i.e, 'NOCHECK' --> never delete, ouch!) the FKs temporarily.
-- Then 'enable' them when you need them back.  
-- Here you go, adapt this to meet your needs.
SET NOCOUNT ON
 
DECLARE @table TABLE(
    RowId INT PRIMARY KEY IDENTITY(1, 1),
    ForeignKeyConstraintName NVARCHAR(50),
    ForeignKeyConstraintTableSchema NVARCHAR(10),
    ForeignKeyConstraintTableName NVARCHAR(50),
    ForeignKeyConstraintColumnName NVARCHAR(50)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT distinct
    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'
-- Remove custom filtering here --
 and U.TABLE_SCHEMA in ('ENTERPRISE','WMWHSE1','WMWHSE2')
-- Dont forget ORDER BY here --
order by U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME
 
-- --SELECT distinct     ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName
-- --  FROM @table
-- --  ORDER BY ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName
 
--Disable CONSTRAINTs
SELECT distinct '--'  ForeignKeyConstraintName, '--' ForeignKeyConstraintTableSchema, '--' ForeignKeyConstraintTableName,
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
WITH NOCHECK NOCHECK CONSTRAINT [' + ForeignKeyConstraintName + ']
GO'
FROM
    @table
  ORDER BY ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName
 
--Enable CONSTRAINTs
SELECT distinct '--'  ForeignKeyConstraintName, '--' ForeignKeyConstraintTableSchema, '--' ForeignKeyConstraintTableName,
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
WITH NOCHECK CHECK CONSTRAINT [' + ForeignKeyConstraintName + ']
GO'
FROM
    @table
   ORDER BY ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName

GO
 

Thursday, March 21, 2013 - 1:21:42 PM - David Read The Tip

When i run this all I get is 'Command(s) completed successfully. I don't get any output.

 

Thanks,


Thursday, March 21, 2013 - 3:31:17 PM - Greg Robidoux Read The Tip

@David - are you sure there are FKs in that database you are running this in?

What version of SQL Server are you using?

 

 


Thursday, March 21, 2013 - 4:16:21 PM - David Read The Tip

Yes, there are as I was trying to truncate a tale and could not due to the constraints. I am on SQL 2008 R2.


Thursday, March 21, 2013 - 5:08:55 PM - Greg Robidoux Read The Tip

@David - try to run this command in your database to see if anything comes back:

SELECT * FROM sys.foreign_keys


Thursday, March 21, 2013 - 5:10:22 PM - Greg Robidoux Read The Tip

@David - one more question.  What permissions do you have in that database?  Are you able to see all objects?


Friday, March 22, 2013 - 1:15:06 PM - David Read The Tip

I get about 14 roes returned. I am sysadmin.


Friday, March 22, 2013 - 2:04:07 PM - Greg Robidoux Read The Tip

@David - do any of these rows look like FKs for the table in question?  Sorry for so many questions.


Saturday, March 23, 2013 - 11:38:40 AM - David Read The Tip

All 15 say Foreign_Key_Constraint in the type_desc column.


Saturday, March 23, 2013 - 6:19:13 PM - Greg Robidoux Read The Tip

@David - feel free to send me an email to gregr@edgewoodsolutions.com and we can see if we can figure this out.

Greg


Wednesday, June 12, 2013 - 2:12:36 PM - Ken Read The Tip

Hi Greg,

Did you get this issue figured out with David? I am having the same problem. I am running SQL 2008 R2 as well. When I check sys.foreign_keys, all of the keys that I was attempting to disable are still showing as enabled. I tried it on multiple tables and got the same results each time. I have sysadmin rights to the box and databases.

Thanks,

Ken


Tuesday, January 07, 2014 - 9:19:14 AM - surojit Read The Tip

Can anyone help me  with a script to  drop and recreate  composite keys as foreign keys for sql server 2005?Urgent reply please..


Tuesday, January 21, 2014 - 11:28:03 AM - bob Read The Tip

OK, So I read this and think interesting, but when will I ever use it.  30 minutes later I am looking at 2 previously written stored procedure of some 1500 lines.  They drop and recreate all foreign keys in a database, after deleting orphaned records, and this is all HARDCODED.  I decide it would be better to modify these foreign keys to CASCADE DELETE, but rather than modify all 1500 lines of code I want to use system tables to pull the details of all foreign keys and rebuild the current foreign keys based on their current settings.  As I look into how I can pull the details of foreign keys from systems tables I find this script below (sorry I did not keep the reference of who or where I got it, but there are many like it)
SELECT
    ConstraintName=fk.name,
    ParentTable=OBJECT_NAME(fk.parent_object_id),
    ParentColumn=c1.name,
    ReferencTable=OBJECT_NAME(fk.referenced_object_id),
    ReferencColumn=c2.name
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
    INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
This was helpfull, but there were 2 rows for each foreign key.  THEN BAM!!!  I bet I can make this work with that code Ilearned this morning...  
LONG STORY SHORT, HERE IS WHAT I CAME UP WITH>>

DECLARE
    @ParentTable varchar(200),
    @Parentkey varchar(200),
    @ReferencTable varchar(200),
    @Referenckey varchar(200),
    @ConstraintName varchar(200),
    @sql varchar(5000)

SELECT  
    @ConstraintName=fk.name,
    @ParentTable=OBJECT_NAME(fk.parent_object_id),
    @ReferencTable=OBJECT_NAME(fk.referenced_object_id),
    @Parentkey=STUFF((SELECT  
            ', '+c1.name
        FROM --sys.foreign_keys fk
            sys.foreign_key_columns fkc --ON fkc.constraint_object_id = fk.object_id
            INNER JOIN sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
            INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
        WHERE fk.object_id = fkc.constraint_object_id
        FOR XML PATH('')),1,2,''),
    @Referenckey=STUFF((SELECT  
            ', '+c2.name
        FROM --sys.foreign_keys fk
            sys.foreign_key_columns fkc --ON fkc.constraint_object_id = fk.object_id
            INNER JOIN sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
            INNER JOIN sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
        WHERE fk.object_id = fkc.constraint_object_id
        FOR XML PATH('')),1,2,'')
FROM sys.foreign_keys fk
WHERE 'ConLink4Contacts'=fk.name
GROUP BY fk.object_id,fk.name,fk.parent_object_id,fk.referenced_object_id

SET @sql = 'ALTER TABLE '+@ParentTable+' WITH NOCHECK ADD CONSTRAINT '+@ConstraintName+'
    FOREIGN KEY ('+@Parentkey+') REFERENCES '+@ReferencTable+'('+@Referenckey+') ON DELETE CASCADE'
--PRINT @sql
EXEC @sql

PS- I am always eager and willing to learn and make things better if you have ideas on how to improve this or a better way, please share.
BobR

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.