![]() |
|
|
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:
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 AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008 |
Running the script
Here are a few sample outputs that you will get running this against the AdventureWorks database.
Example 1:
| 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:
| 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
| Share: | Share | Tweet |
|
![]() |
![]() |
Connect with MSSQLTips.com |
| 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, 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 sysnameDECLARE 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_idOrder By TbR.nameOPEN cursor_tblfindFETCH NEXT FROM cursor_tblfind INTO @PKTABLE_NAME_RealWHILE @@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_RealEND CLOSE cursor_tblfindDEALLOCATE 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 |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |