SQLServerCentral Article

Foreign Keys

,

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!

Rate

4.6 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.6 (5)

You rated this post out of 5. Change rating