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

Top SQL Server Tools

SQL safe backup

SQL compliance manager

ApexSQL Log

SQL Sentry Performance Advisor

SQL Source Control

Disable, enable, drop and recreate SQL Server Foreign Keys

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

Free SQL Learning - Backup compression and storage deduplication: A perfect match?

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)

Last Update: 11/19/2007

About the author

Become a paid author

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

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

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now

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

Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial!

Demystify TempDB Performance and Manageability