Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Disable, enable, drop and recreate SQL Server Foreign Keys


By:   |   Read Comments (22)   |   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:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, February 25, 2016 - 8:01:30 PM - Faisal Abbas Back To Top

Very nice script.

I had made a little change to the script to generate scripts for multiple tables at the same time

SET @tableName = '%'  

SET @schemaName = 'dbo'  

...

  WHERE   TbR.name LIKE @tableName                                      -- Changed from    WHERE   TbR.name = @tableName  

           AND schema_name(TbR.schema_id) Like @schemaName  -- Changed from           AND schema_name(TbR.schema_id) = @schemaName  

...

       WHERE   TbR.name = @PKTABLE_NAME                               -- Changed from WHERE   TbR.name = @tableName  

               AND schema_name(TbR.schema_id) = @schemaName  

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

 

Now the Script look like

-- 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 = '%'  

SET @schemaName = 'dbo'  

 

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 LIKE @tableName  

           AND schema_name(TbR.schema_id) Like @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 = @PKTABLE_NAME 

               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 


Tuesday, January 21, 2014 - 11:28:03 AM - bob Back To Top

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

 

 


Tuesday, January 07, 2014 - 9:19:14 AM - surojit Back To Top

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


Wednesday, June 12, 2013 - 2:12:36 PM - Ken Back To Top

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


Saturday, March 23, 2013 - 6:19:13 PM - Greg Robidoux Back To Top

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

Greg


Saturday, March 23, 2013 - 11:38:40 AM - David Back To Top

All 15 say Foreign_Key_Constraint in the type_desc column.


Friday, March 22, 2013 - 2:04:07 PM - Greg Robidoux Back To Top

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


Friday, March 22, 2013 - 1:15:06 PM - David Back To Top

I get about 14 roes returned. I am sysadmin.


Thursday, March 21, 2013 - 5:10:22 PM - Greg Robidoux Back To Top

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


Thursday, March 21, 2013 - 5:08:55 PM - Greg Robidoux Back To Top

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

SELECT * FROM sys.foreign_keys


Thursday, March 21, 2013 - 4:16:21 PM - David Back To Top

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 - 3:31:17 PM - Greg Robidoux Back To Top

@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 - 1:21:42 PM - David Back To Top

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

 

Thanks,


Sunday, October 14, 2012 - 11:16:31 PM - Darrin Wolf Back To Top
-- 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
 

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

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

--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 - 5:28:30 AM - Amir Hussein Samiani Back To Top

  --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 - 5:20:23 AM - Amir Hussein Samiani Back To Top

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


Wednesday, August 05, 2009 - 2:59:59 PM - scolvard Back To Top

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


Thursday, June 12, 2008 - 12:20:15 PM - admin Back To Top

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

Thanks


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

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, March 13, 2008 - 5:34:15 AM - jtgooding Back To Top

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


Learn more about SQL Server tools