inforakesha

A fine WordPress.com site

Foreign Keys and their States

Leave a comment

Foreign keys enforce referential integrity and in this way contribute to the database’s consistency. However, sometimes it is necessary to disable them temporarily. I have seen some confusion as to how one re-enables these disabled constraints in such a way that the database’s consistency remains unaffected. So, in this article, I will examine some rarely explored areas that concern foreign keys; in particular, I will look at disabled and un-trusted foreign keys.

Why would one ever disable a foreign key?

An example for the need to temporarily disable foreign keys is when one wants to load a large batch of consistent data into a set of tables that reference each other via foreign keys. It is easier to load data on a per table basis, and if the foreign keys are disabled, one can load the table data for each table. For example if there is a foreign key set up on a table referencingTable and a referencedTable, one should start loading data for the referencedTable first. But foreign key relationships can be complex, and sometimes even the order in which data rows are added to a single table is significant. And, if we have millions of rows of data, and we are confident that they are consistent, disabling foreign keys is the best option.

After the large data batch is loaded, the database should once again enforce its consistency rules, and the foreign keys should be restored to their original states.

I will use a simple two table example to demonstrate how to enable and disable foreign keys, and how to detect inconsistencies in the database. The two tables are tableReferenced and tableReferencing. As the name suggests the tableReferencing will reference the tableReferenced table.

To create and populate the two tables run:

CREATE TABLE tableReferenced
( colA INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, colB NVARCHAR(20) DEFAULT N’some data’
)
GO

INSERT INTO tableReferenced DEFAULT VALUES
INSERT INTO tableReferenced DEFAULT VALUES
GO

CREATE TABLE tableReferencing
(
colC INT NOT NULL
PRIMARY KEY
IDENTITY(2, 2),
colARef INT NOT NULL
CONSTRAINT FK_References
REFERENCES tableReferenced ( colA )
)
GO

INSERT INTO tableReferencing VALUES ( 1 )
INSERT INTO tableReferencing VALUES ( 1 )
INSERT INTO tableReferencing VALUES ( 2 )

GO

This will create the two tables and populates them like this:

The foreign key is established, and one can check this by inserting a row that would violate referential integrity:

INSERT INTO tableReferencing VALUES ( 1000 )

This insert should fail, because there is no corresponding row in tableReferenced. SQL Server, as expected, reacts with the following error message:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint
“FK_References”. The conflict occurred in database “FKplay”,
table “dbo.tableReferenced”, column ‘colA’.

The statement has been terminated.

To disable a foreign key in a SQL Server database, you can use a simple ALTER statement such as:

ALTER TABLE tableReferencing NOCHECK CONSTRAINT FK_References

Following this statement on can insert the following row that would violate the referential integrity:

INSERT INTO tableReferencing VALUES ( 1000 )

SQL Server confirms this, and the row has been inserted. No error messages were sent.

The data in our tables is now inconsistent, and looks like this

And this is the point where many DBAs get confused about how to re-enable the foreign key. The statement I have seen used most often looks like this:

ALTER TABLE tableReferencing CHECK CONSTRAINT FK_References

This seems to be the most obvious statement, since it just replaces the NOCHECK in the disabling statement with CHECK.

If we now try to insert a new row into the table that refers to a non-existent row, as follows:

INSERT INTO tableReferencing VALUES ( 1001 )

We get the expected error message, which would seem to indicate that the foreign key is working as expected.

However, if the data insertions that were performed during the period the foreign key was disabled have left the database table in an inconsistent state, we do not see an error immediately. In the above example, we have a row in the tableReferencing table that is referencing a row with id 1000 in the tableReferences table. This row does not exist. If we execute a select statement that joins the two tables, we still get results, e.g. the following query executes without any errors:

SELECT *
FROM tableReferencing
INNER JOIN tableReferenced ON tableReferencing.colARef = tableReferenced.colA

It will not return anything for the violating row. However, other queries may be less lucky, and applications may not be ready to handle their results.

One can easily check if the table is consistent, and whether referential integrity is satisfied, by executing:

DBCC CHECKCONSTRAINTS (‘tableReferencing’)

In our case it returns:

Table Constraint Where
————————- —————- ——————-
[dbo].[tableReferencing] [FK_References] [colARef] = ‘1000’
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Indicating that there is a referential integrity violation.

–To enabling the constraint again you will get error
ALTER TABLE table Referencing CHECK CONSTRAINT FK_References

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s