I received a request to export data from a table in a database in Production to a similar table in a database in the Development environment. I used the export/import wizard thru SQL Server Management Studio but my export was failing giving me an error that the data cannot be copied because Foreign Key was present in the destination database. In the past I would just script out a drop and create script for all the foreign keys then drop all the Foreign Keys, do the data export and then re-create the Foreign Keys. After some research online I came across a better option to just disable the Foreign Keys instead of dropping and recreating them.
But first lets understand what is a Primary Key and Foreign Key.
In SQL Server, a primary key is a single field that has a unique value to define a record. Fields that are part of the primary key cannot contain a null value. A table can have only one primary key. Usually the primary key is used as an index but this can vary.
A table can have only ONE primary key and this primary key can consist of single or multiple columns (fields).
Since primary key constraints ensure unique data, they are often called identity columns.
When you designate a primary key constraint for a table, the SQL engine enforces data uniqueness by auto create a unique index for the primary key columns.
A foreign key is a column or set of columns that allows developers to establish a referential link between the data in two different tables. This link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.
A FOREIGN KEY is a field (or collection of fields) in one table, that links to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced table (parent table).
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column in the child table, because it has to be one of the values contained in the parent table.
Based on the developer’s coding standard – usually it is a good practice to prefix with FK_{FK name} and the same goes with Primary Keys being prefixed with PK_{PK Name}
The following SQL query creates a FOREIGN KEY on the “PersonID” column in the Persons table when the “Orders” table is created:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
If the Orders table is already created then use this SQL query to create a FOREIGN KEY constraint on the “PersonID” column:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
If you need to name a Foreign Key constraint and to specify a Foreign Key constraint on multiple columns, use this SQL Query:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
You can disable a Foreign Key in a table using the Alter Table statement in SQL Server Management Studio. Here is the syntax to disable a foreign key in SQL Server (T-SQL):
ALTER TABLE [your_table_name]
NOCHECK CONSTRAINT [your_fk_name];
Parameters/Syntax:
your_table_name
The name of the table where the foreign key has been created.
your_fk_name
The name of the foreign key that you wish to disable.
The above script would use the ALTER TABLE statement to disable the constraint called fk_inyour_fk_name on the your_table_name table.
After you have disabled the Foreign Key then you should be able to do your data load using the Daa with no error.
To disable all constraints
— disable all constraints
EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
To turn the constraints back on – the print command is optional and it is just for listing the database tables.
Run this:
— enable all constraints
exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
To disable the constraints is much helpful when you have to copy data from one database to another. I prefer this then dropping constraints.
If you have triggers in the database then you will have to disable the triggers prior to your data load and then add the triggers back on once the data load is completed.
To disable all constraints and triggers run this:
sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
To enable all constraints and triggers run this:
exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”
The word of caution is disabling constraints and triggers – to you have make sure there are no new deltas being written to the database by the users because once you disable all the constraints and triggers any new deltas written to the database that might violate the integrity of the database. Hence you have to ensure that all application traffic is stopped.
Also if you need to import a large amount of data, then consider using BULK INSERT because this method does not fire the triggers. However after your bulk insert it completed, you will need to fix any data integrity issues which occurred during your bulk insert that circumvented the trigger policies.
Hope this helps clarify the concept of Primary Keys, Foreign Keys and Constraints.