Checking Foreign Keys

Published 19 September 09 08:38 PM | MartinBell
A while ago I answered a question in the news groups where someone was having problems doing a data load because of the foreign key constraints on a table. To overcome this you can disable these constraints, but then you have to be wary because it is possible to have loaded data that is orphaned.

To show this look at the following example. We have two tables and a foreign key linking them.

USE [tempdb]
GO

IF
  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_example_referencing]') AND type in (N'U'))
            DROP TABLE [dbo].[FK_example_referencing] ;
GO

IF
  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_example_referenced]') AND type in (N'U'))
            DROP TABLE [dbo].[FK_example_referenced] ;
GO

CREATE
TABLE FK_example_referenced
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referenced PRIMARY KEY,
    name VARCHAR(10) NOT NULL
);
GO

CREATE
TABLE FK_example_referencing
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referencing PRIMARY KEY,
    fid int not null CONSTRAINT FK_FK_example_referenced FOREIGN KEY REFERENCES
FK_example_referenced
( id ),
    val char(10) NOT NULL
);
GO

If you are not careful on the order in which data is loaded you may want to load data into the referencing table that does not exist in the referenced table.

INSERT INTO FK_example_referencing(fid, val) VALUES ( 1, 'First' );
GO

Will give you the error:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.
The statement has been terminated.

So you may need to add data to the referenced table before inserting data into the referencing table:

INSERT INTO FK_example_referenced (name) VALUES ( 'One' );
GO

SELECT
* FROM FK_example_referenced ;
GO

id          name
----------- ----------
1           One

INSERT INTO FK_example_referencing(fid,val) VALUES ( 1, 'First' );
GO -- Now Works

SELECT * FROM FK_example_referencing ;
GO

id          fid         val
----------- ----------- ----------
2           1           First    

Another option to avoid having to load tables in order is to disable the foreign key constraint and then re-enable this when you have finished. You can do this using the statement:

-- Disable the constraint.
ALTER
TABLE FK_example_referencing NOCHECK CONSTRAINT FK_FK_example_referenced;
GO

Once it is disabled you can then insert data that does not exist in the referenced table.

INSERT INTO FK_example_referencing(fid, val) VALUES ( 2, 'Second' );
GO

SELECT
* FROM FK_example_referencing
;
GO

id          fid         val
----------- ----------- ----------
2           1           First    
3           2           Second   


If you then re-enable the constraint it will not check the validity of the data unless you specify the the WITH CHECK option, and without this option you can not guarantee the existing data will satisfy the Foreign Key constraint.

-- Re-enable the constraint.
ALTER
TABLE FK_example_referencing CHECK CONSTRAINT FK_FK_example_referenced;
GO

Subsequent insert statements will then check the Foreign Key constraint and validate the data, but existing data will remain. 

INSERT INTO FK_example_referencing(fid,val) VALUES ( 3, 'Third' );
GO
– Fails

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.

The statement has been terminated.

If you try to re-enable the constraint using WITH CHECK and there is some data that violates the Foreign Key constraint enabling the constraint will fail.

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT FK_FK_example_referenced;
GO

Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.

So how can you be sure that this has not happened on a system you have inherited?

In the system table sys.foreign_keys there is a column is_not_trusted that will have a value of 1 if the data has not been validated against the constraint. This column will be remain as 1 when a constraint has been enabled but not checked.

SELECT name,is_not_trusted
FROM sys.foreign_keys ;
GO

name                       is_not_trusted
------------------------- --------------
FK_FK_example_referenced  1

So once the data is in order and the Foreign Key has been checked the value in of is_not_trusted in sys.foreign_keys will then be 0.

INSERT INTO FK_example_referenced (name) VALUES ( 'Two' ), ( 'Three' );
GO

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT FK_FK_example_referenced
;
GO
– Works

SELECT name,is_not_trusted
FROM sys.foreign_keys
;
GO

name                      is_not_trusted
------------------------- --------------
FK_FK_example_referenced  0


If you disable an index (e.g. PRIMARY KEY index), all foreign keys referencing the index will be disabled.

ALTER INDEX PK_FK_example_referenced ON [dbo].[FK_example_referenced] DISABLE
GO
 
Warning: Foreign key 'FK_FK_example_referenced' on table 'FK_example_referencing' referencing table 'FK_example_referenced' was disabled as a result of disabling the index 'PK_FK_example_referenced'.

For a data load exercise this  is probably not going to be much use as any attempt to INSERT/UPDATE/DELETE or SELECT any data in a clustered index will fail, but you will be able to insert data into the referenced table.

INSERT INTO FK_example_referencing(fid,val) VALUES ( 4, 'Forth' );
GO -- Works

To enable the index you will need to rebuild it:

ALTER INDEX PK_FK_example_referenced ON [dbo].[FK_example_referenced] REBUILD
GO

But this will not re-enable the foreign key and you can still insert data.

INSERT
INTO FK_example_referencing(fid,val) VALUES ( 5, 'Fifth' );
GO -- Works

SELECT name,is_not_trusted
FROM sys.foreign_keys ;
GO

name                       is_not_trusted
------------------------- --------------
FK_FK_example_referenced  1

Disabling a clustered index will also disable any non-clustered indexes on that table. You will also need to re-enable these by either re-building them individually or using the ALTER INDEX ALL ... REBUILD statement e.g:

ALTER INDEX ALL ON [dbo].[FK_example_referenced] REBUILD

GO

If the clustered index is disabled you will get an error message when you try to rebuild a non-clustered index on that table.

This is covered in the Books Online topic “Guidelines for Disabling Indexes” (which should really be called “Guidelines for Disabling Indexes and Constraints”) and it’s sister topic “Guidelines for Enabling Indexes and Constraints”

 

Comments

# Dew Drop – Weekend Edition – September 19-20, 2009 | Alvin Ashcraft's Morning Dew said on September 20, 2009 05:57 PM:

Pingback from  Dew Drop – Weekend Edition – September 19-20, 2009 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs

Syndication