If you don’t have a key, you don’t have a table!
                                          - Joe Celko

Foreign Keys

For a long time I didn't gave much importance to foreign keys (FK). Occasionally they were a pain while deleting or inserting ,some times while truncating. Mostly they were harmless and their presence was ignored. Some time back for no apparent reason I developed a genuine interest into them and started collecting what ever possible information.

This is not a know-it-all article, but a collection of loosely coupled scripts organized at a single place. I am sure that there is more to it and expecting some of that in the feedback.

One of the most important assets of an organization is the DATA and if it is not proper the result will be chaos. Having data in a CHILD table without a reference to MASTER is unimaginable. FK's will be handy here.

Terminology

MASTER TABLE - Table having Primary Key (PK)

CHILD TABLE - Table having Foreign Key (FK)

Since the objective is to understand the concept,I have used thin tables with simple identifier names that

may not satisfy any standard nomenclature!

Where does FK's fit?

SQL Server 2000 supports 5 types of constraints, NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY. FK constraints identify the relationships between tables. A foreign key in one table points to a candidate key (PK) in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value.

Quick Check

Execute the following SQL in your production DB,

SELECT count(*) FROM sysreferences

If the o/p is zero or near to it, then you may want to consider creating some FK's!

Meta Data

Other than sysobjects most of the information on reference keys is stored in following three system tables,

• sysreferences

• sysconstraints

• sysforeignkeys

Archipelago

Without FKs, tables will be similar to a group of islands. Following script will identify these tables,

SELECT name [Tables NOT having FK]
  FROM sysobjects
 WHERE xtype='U'
   AND objectproperty(object_id(name),'TableHasForeignKey')= 0
 ORDER BY 1
 

Creating a FK (Column level)

I am including this section only for the sake of completion. Hence not much on the topic!

CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
                             FOREIGN KEY (fkey) REFERENCES master(pkey))

Note: If you ignore CONSTRAINT fk_master_child from the above code SQL Server will create FK

with its own name. The name may not be meaningful!

Creating a FK (Table level)

In case the FK is a COMPOSITE KEY then it can not be defined on column level. A COMPOSITE KEY is the one

which has more than one columns in it. This key uniquely identifies records in the MASTER table. Composite

keys need to be defined on table level.

CREATE TABLE master(
 pkey1 int
,pkey2 int
,data varchar(10)
,CONSTRAINT pk_master PRIMARY KEY (pkey1,pkey2))
GO
CREATE TABLE child (
 fkey1 int
,fkey2 int
,CONSTRAINT fk_master_child FOREIGN KEY (fkey1,fkey2) REFERENCES master(pkey1,pkey2))
GO

ON DELETE CASCADE

Once FK is set, you can NOT delete records from MASTER, if a CHILD table is referring the same records by a FK.

This feature can be overridden using ON DELETE CASCADE. This option deletes respective records from CHILD, when DELETE is executed on the MASTER.

CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
                             FOREIGN KEY (fkey)
                             REFERENCES master(pkey) ON DELETE CASCADE )
Go
INSERT master
SELECT 1, 'a'
GO
INSERT child
SELECT 1
GO
DELETE master
 WHERE pkey = 1
GO
SELECT * FROM child

Note: CASCADE can also be used for UPDATE operations.

ON DELETE CASCADE can be replaced by the default option, ON DELETE NO ACTION which will raise an error

on FK violation.

FK referencing PK on a same table

A FK can refer to a PK on the same table as below.

CREATE TABLE master(
 emp_id   int PRIMARY KEY
,emp_name varchar(10)
,mgr_id   int CONSTRAINT fk_master FOREIGN KEY (mgr_id) REFERENCES master(emp_id)
)

Note: An employee/Manager scenario is an example for the above case.

FK is PK

CREATE TABLE master(
 pkey int PRIMARY KEY
,data varchar(10))
CREATE TABLE child(
 fkey int PRIMARY KEY FOREIGN KEY (fkey) REFERENCES master(pkey))

The PK of CHILD table is also a FK to MASTER table. Since there is a PK on the CHILD table it can not have duplicate

records Hence for each record in MASTER there will be maximum 1 or 0 records in CHILD.

In case the number of records in both the tables are same, that means there is an 1:1 relationship between the tables. Hence there is non need for CHILD table, the columns in the CHILD can be moved to MASTER.

Recursive

I can not imagine any practical use of below code, but theoretically it is possible.

CREATE TABLE master(
 pkey int PRIMARY KEY FOREIGN KEY (pkey) REFERENCES master(pkey))

Dependants

sp_fkeys system SP lists the CHILD tables for a given MASTER table.

sp_fkeys 'table_name'
GO

Following code list all the tables participating in a relationship.

SELECT object_name(fkeyid) AS child
      ,object_name(constid)AS FK
      ,object_name(rkeyid) AS master
 FROM sysreferences
ORDER BY 1

Similar to above code following script list the columns participating in a relationship.

SELECT object_name(rkeyid) Master_Table
      ,sc2.name Master_Cols
      ,object_name(fkeyid) Child_Table
      ,sc1.name Child_Cols
      ,sf.keyno Col_Order
 FROM sysforeignkeys  sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno

Trees and Forests

Tree view will give a hierarchical representation, which will be easier to understand.

For e.g.
World
   North America
      Canada
      United States
         Washington
            Redmond
         New York
            New York City
   Europe
      France
         Paris

The following code gives a tree view for a parent table. The code is from BOL, I have done miner changes to fulfill our requirement. Search for "Expanding Hierarchies" in BOL for the actual code

CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
      BEGIN
         SELECT @current = item
         FROM #stack
         WHERE level = @level
         SELECT @line = space(@level - 1) + @current
         PRINT @line
         DELETE FROM #stack
         WHERE level = @level
            AND item = @current
         INSERT #stack
            SELECT object_name(fkeyid), @level + 1
            FROM sysreferences
            WHERE object_name(rkeyid) = @current
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1
END -- WHILE

Lets test the SP,

CREATE TABLE grand_parent(id int PRIMARY KEY )
GO
CREATE TABLE parent      (id int PRIMARY KEY  FOREIGN KEY  (id) REFERENCES grand_parent(id))
GO
CREATE TABLE child       (id int PRIMARY KEY  FOREIGN KEY  (id) REFERENCES parent(id))
GO

e.g. expand 'grand_parent'
grand_parent
   parent
     child

Enable/Disable FK

Following code can be used to disable a FK,

ALTER TABLE <table_name> NOCHECK CONSTRAINT <constraint_name>

There may be a case when you want to disable all the FKs in a DB temporarily, Mostly while massaging the data on a staging server. The following code will do exactly that,

SET QUOTED_IDENTIFIER OFF
GO
-- Disable
EXECUTE sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO
-- Enable
EXECUTE sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
GO

Along with the FK's the above code will disable other constraints also. Be careful if you don't want to do that!

Note: You can not modify the definition of a column which is participating in a relationship even if FK constraint is disabled.

Trivia

All the following SQLs return 0 OR 1 depending on the property existing in the DB.

-- To check whether the FK is enabled or NOT?

SELECT OBJECTPROPERTY(object_id('myforeignkey'), 'CnstIsDisabled')

-- To check whether the Table has FK?

SELECT OBJECTPROPERTY ( object_id('eRisk_UserRole'),'TableHasForeignKey')

-- To check whether the Table has PK?

SELECT OBJECTPROPERTY ( object_id('eRisk_UserRole'),'TableHasPrimaryKey')

TRUNCATE

You can NOT TRUNCATE a MASTER table even though the CHILD table doesn't have any referencing record.

CREATE TABLE master(
 pkey int PRIMARY KEY
,data varchar(10))
GO
CREATE TABLE child(
 fkey int FOREIGN KEY (fkey) REFERENCES master(pkey))

Even though both the table are empty, truncating the MASTER table will raise an error.

TRUNCATE TABLE master

Server: Msg 4712, Level 16, State 1, Line 1

Cannot truncate table 'master' because it is being referenced by a FOREIGN KEY constraint. 

Conclusion

Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application. This is to save the time which DB server needs to check for the constraints. I don't think the time saved is really worth it!

Some time back I was involved in a migration project where the source DB didn't had any FK's but the destination DB was full of them. I had a tough time clearing the orphaned records. Finally now we have a clean DB, that's what matters in the long run!