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.
At the moment we may think that the foreign key is back to the original state, i.e. the state that we had before we disabled it. However, this is not the case. The foreign key that we have now is in a so called “untrusted” state. You can see this by looking at the sys.foreign_keys system view on SQL Server 2005, or at the status column of the sysobjects table on SQL Server 2000.
If we wanted to create a new foreign key that was in exactly the same state as the current one, we would need to execute:
ALTER TABLE tableReferencing WITH NOCHECK
ADD CONSTRAINT FK_References FOREIGN KEY ( colARef )
REFERENCES dbo.tableReferenced ( colA )
Note the WITH NOCHECK part.
When we re-enabled the disabled foreign key, SQL Server did not perform checks to ensure that the tables involved in the foreign key relationship satisfied the constraints imposed by that foreign key. This made that operation fast, since SQL Server did not need to scan the relevant tables, but this was at the expense of database integrity.
To change the foreign key back to its original state, we need to let SQL Server check the validity of the constraint. To do this, the statement we should have used is:
ALTER TABLE tableReferencing WITH CHECK CHECK CONSTRAINT FK_References
This statement will make SQL Server check the data, and in our case it will find that the data in the tables is inconsistent with the foreign key constraint. On fixing these issues (in this case just dropping the violating row from the table tableReferencing), the state of the foreign key is restored to its original state. This will flip back the is_trusted flag in sys.foreign_keys, as well as enabling the constraint.
So to see which foreign keys are enabled or disabled, you can check the sys.foreign_keys table on 2005. It has a is_disabled column that has the value of 1 if the foreign key is disabled. Similarly you can get the information about whether or not a constraint is trusted.
Summary
When you disable a foreign key, not only will SQL Server disable it, it will, in addition, flip its state to non-trusted. When you enable it with the CHECK keyword, the trustedness of the foreign key is not restored. You can alter a foreign key’s state using an alter statement on the table. The CHECK and NOCHECK parts control whether the foreign key is enabled or disabled. The WITH CHECK and WITH NOCHECK options control whether SQL Server should check the consistency of the tables involved, and this way establish whether the foreign key is trusted or not. You can also use DBCC CHECKCONSTRAINTS to check whether the referential integrity constraint is satisfied on a particular table.
Load comments