SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

Earlier I wrote a blog post about how to Disable and Enable all the Foreign Key Constraint in the Database. It is a very popular article. However, there are some scenarios when user needs to drop and recreate the foreign constraints. Here is a fantastic blog comment by SQL Expert Swastik Mishra. He has written a script which drops all the foreign key constraints and recreates them. He uses temporary tables to select the existing foreign keys and respective column name and table name. Then determine the primary key table and column name and accordingly drop and recreate them.

Here is a script by Swastik and it works great.

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

Thanks Swastik for a great script. Swastik, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Listing Primary Key of Table with Stored Procedure sp_pkeys

Here is a follow up blog post of my earlier blog post on very similar subject Listing Foreign Key Relationships of Table with Stored Procedure sp_fkeys. In previous blog post we listed foreign keys and now we will list the primary key of the table with the help of Stored Procedure sp_fkeys.

Here is a quick script how you can use stored procedure sp_pkeys to list the primary key. This stored procedure takes a minimum of two parameters a) name of the table, b) name of the schema.

USE AdventureWorks2012;
GO
EXEC sp_pkeys @table_name = N'Employee',
@table_owner = N'HumanResources';

Here is the output generated from the script listed above:

Here is an alternate way to list the keys as well - Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Listing Foreign Key Relationships of Table with Stored Procedure sp_fkeys

A very cool trick which I have previously not shared on my blog regarding how to list all the foreign key relationship of table with the help of Stored Procedure sp_fkeys.

Here is a quick script how you can use stored procedure sp_fkeys to list all the foreign key relationship. This stored procedure takes a minimum of two parameters a) name of the table, b) name of the schema.

USE AdventureWorks2012;
GO
EXEC sp_fkeys @pktable_name = N'Employee',
@pktable_owner = N'HumanResources';

Here is the output generated from the script listed above:

Here is an alternate way to list the keys as well - Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Create Unique Constraint on Table Column on Existing Table

Here is the question I received on twitter.

“Can we create a unique constraint on table column on Existing Table?”

Of course Yes!

Here is how you can create a unique constraint on the table which already exist in our system.

USE tempdb
GO
-- Create Table
CREATE TABLE Table1 (ID INT, Col1 VARCHAR(100))
GO
-- Alter Table Create Constraint
ALTER TABLE Table1
ADD CONSTRAINT UX_Constraint UNIQUE (Col1)
GO
-- Clean up
DROP TABLE Table1
GO

If your table already exists you can use above method to create the constraint. However, if you are about to create tables, you can just specify UNIQUE in the schema definition of Create Table itself. We will discuss about this in a future post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

Here is an email I received during the weekend.

“Hi Pinal,

I am a senior tester in the leading organization and we have two different environments 1) Testing 2) Production.

As a part of the testing we want to insert garbage data into the database system and see how the application behaves in this scenario. However, there is a small problem. Everytime when I try to insert garbage data in the database system the tables start giving me error that due to constraints on the table, I need to populate data in certain order and it has to be correct. Actually, we want to populate the bad data and see how application reacts to it. Upon talking to development team regarding this they suggested that we should skip this test as due to contraints there will be never bad data. Though, I understand their viewpoint, I must document how the application behaves when there are bad data and data integrity is compromised.

Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”

This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.

There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: 1505 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and the index name

Here is another example where the error messages are very clear but often developers get confused with the message. I think the reason for the confusion is the word “Key” used in the error message. After I explained this to a developer who sent me the error he realize that it is about how we all interpret a same statement.

Following code will generate the error 1505.

-- Create Table
CREATE TABLE test (ID INT NOT NULL,
Col1 INT, Col2 VARCHAR(100))
GO
-- Populate Table
INSERT INTO test (ID, Col1, Col2)
SELECT 1, 1, 'First'
UNION ALL
SELECT 1, 2, 'Second' -- Duplicate ID col
UNION ALL
SELECT 3, 3, 'Third'
UNION ALL
SELECT 4, 4, 'Fourth'
GO

After creating a table, I am inserting same a key in the first column multiple times.

-- Now create PK on ID Col
ALTER TABLE test
ADD CONSTRAINT [PK_test]
PRIMARY KEY CLUSTERED
([ID] ASC)
GO

Now when I attempt to create a Primary Key on the column it gives us following error.

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.test’ and the index name ‘PK_test’. The duplicate key value is (1).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.

Fix / Workaround / Solution:

In SQL Server Primary Key can’t have duplicate records as well can’t have a column which is nullable. In our case, you can notice that we are creating a primary key on the column ID which contains duplicate values of 1. The only way to create primary key on this column is to delete the duplicate row which exists. If your business logic does not allow to delete the duplicate row, this means that your column is not a good candidate for the Primary Key and you will have to either select another column or use a composite Primary Key (where you use multiple columns).

On Separate note, here is the blog post with video which explains how you can delete the duplicate row from the table: Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to ALTER CONSTRAINT

After reading my earlier blog post SQL SERVER – Prevent Constraint to Allow NULL. I recently received question from user regarding how to alter the constraint.

No. We cannot alter the constraint, only thing we can do is drop and recreate it.

Here is the CREATE and DROP script.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT, Col1 INT, Col2 INT)
GO
-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1
CHECK (Col1 > 0)
GO
-- Dropping Constraint on Col1
ALTER TABLE TestTable DROP CONSTRAINT CK_TestTable_Col1
GO
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO

If you try to alter the constraint it will throw error.

Reference: Pinal Dave (http://blog.SQLAuthority.com)