BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Foreign Keys

The eighteenth part of the SQL Server Programming Fundamentals tutorial examines the use of foreign key constraints. These constraints define relationships between two tables, enforcing referential integrity to avoid orphaned rows in normalised tables.

What is a Foreign Key?

When working with a relational database that has been normalised there are, in all except the simples cases, multiple tables containing related information. As we have seen in an earlier article, the normalisation process prevents data anomalies caused by duplication. However, simply extracting information into multiple tables to avoid this replication presents the potential of referential integrity problems.

A referential integrity problem occurs when two tables are logically linked but where one table refers to data in another that does not exist, either because it was never created or has been deleted. To enforce correct referential integrity, you can create one or more foreign key relationships.

Each foreign key specifies a link between a parent table and a child table based upon one or more compatible columns. The parent table's part of the foreign key must be a candidate key. This can either be its primary key or a unique constraint. The child table's part of the relationship is known as the foreign key. Once created, the relationship enforces the referential integrity of the link in two ways. Firstly, it prevents rows in the parent table being deleted if they are referenced in the foreign key of the child table. Secondly, non-null values cannot be stored in the child table's foreign key columns unless matching values exist in the parent. Null values can be held in a foreign key if the columns permit this.

In most cases, the parent and child tables of a foreign key relationship are separate tables. However, it is quite acceptable to create a relationship where the candidate key and the foreign key are within the same table. This is known as a self-referencing or recursive key and is ideal for creating hierarchical relationships between entities of the same type.

In SQL Server databases, you can create multiple foreign key relationships for the same table. Each foreign key can reference the same candidate key in the same parent table if desired, or you can link to several different tables from the same child table. Although there is no enforced limit on foreign keys, Microsoft recommends that you create no more than two hundred and fifty-three foreign keys for a single child table and reference a single parent table from no more than two hundred and fifty-three children.

Foreign key columns tend to be used heavily in queries, as they are often the linking columns of joins. However, by default SQL Server will not create an index on the foreign key columns of the child table. It is usually beneficial to manually add such an index.

Many-to-Many Relationships

Foreign keys create one-to-many relationships. These are so-named because one row in the parent table can be linked to many rows in the child table. However, each entry in the child table may only be linked to a single row in the parent. Another type of relationship is the many-to-many link. In this scenario, many rows in either the parent or child table may link to many rows in the other table.

Many-to-many relationships cannot be created directly in most relational database management systems, including SQL Server. To create this type of link, a third table is required. This table, known as a junction table, includes a foreign key to each of the tables that you wish to link. The combination of two one-to-many relationships has the effect of a single many-to-many link.

You can see how a many-to-many link operates below. In this case the Employees table links to the Skills table via a junction table. This allows each employee to have multiple skills and each skill to be assigned to any number of employees. In the example, Chris Green is proficient in both C# and Visual Basic. The C# skill can be provided by two employees: Arthur Brown and Chris Green. Note also that it is possible for a row to have no related items, as in the case of the SQL Server skill.

Employees Table

EmployeeIDName
ABArthur Brown
CGChris Green
LBLouise Black

Junction Table

EmployeeIDSkillID
ABCS
CGCS
CGVB
LBVB

Skills Table

SkillIDSkill
CSC#
VBVisual Basic
SQLSQL Server

Cascading Updates and Deletes

If you try to update the values in the primary key or unique constraint that is used in the parent table of a foreign key relationship, and the child table has rows that reference the values being changed, the default response is an error. Similarly, if you try to delete a row from the parent table that is referenced by one or more rows in the child table, an error occurs. You can modify this behaviour with cascading updates and deletes.

When a foreign key includes cascading updates, changes to the parent table's candidate key do not automatically cause an error. Instead, all of the linked rows in the child table are updated. The new values for the child row's foreign key columns vary according to the cascading update setting:

  • No Action. This is the default setting, which does not allow cascading updates.
  • Cascade. When updates are set to cascade, a change in the candidate key of the parent table causes all matching rows in the child table to be updated with the same value, correctly retaining all links.
  • Set NULL. With this setting, any change to a parent's key changes matching foreign key values to NULL.
  • Set Default. When using this option, changes to a parent's key causes matching foreign key values to be changed to their default values.

Cascading deletes are similar to cascading updates. When configured, deleting a row in the parent table causes matching rows in the child table to be deleted, or matching foreign key columns to be set to NULL or default values. The actual action depends upon the setting selected.

23 December 2008