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.

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.

Adding a Foreign Key to a Database

In this article we will consider two methods of adding foreign keys to a database. These will be using the SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) commands.

We will add the foreign keys to tables in the JoBS tutorial database. This has been created over the course of the tutorial so far. To prepare the tutorial database, download and run the script via the link at the start of this page. This will create the database and some sample data.

Adding a Foreign Key Using SQL Server Management Studio

Foreign keys can be created, edited and deleted using the graphical user interface of SQL Server Management Studio (SSMS). We will start by adding a new relationship between customers and their addresses. The CustomerAddresses table already includes a column named CustomerNumber that defines which customer each address belongs to. This column will be the foreign key that is related to the Customers table's primary key. To begin, right-click the CustomerAddresses table in the Object Explorer and choose "Design" from the menu that appears. The table designer will be displayed.

To create a new foreign key, choose "Relationships..." from the Table Designer menu. You can also click the Relationships toolbar button. The "Foreign Key Relationships" dialog box will be displayed. Initially this will contain no foreign keys as none have yet been created. To add the first, click the Add button.

To create the new foreign key relationship we will first specify the linking columns. To do so, click the Tables and Columns Specification option. An ellipsis button will appear in the property box. Click the button to display the Tables and Columns selection dialog box.

The Tables and Columns dialog box is used to select the parent and child tables and the candidate key and foreign key columns to be linked. The foreign key table cannot be changed, as it is the table that is currently being edited. From the primary key table drop-down box, select "Customers".

With the two tables selected, you must select one or more columns from the parent table in the left column of the grid and then select the matching foreign key columns in the right column. In this case, select CustomerNumber in both columns, as shown in the figure above. Click the OK button to store the selections and return to the previous dialog box.

The remaining options for the new foreign key relationship are as follows:

• Check Existing Data on Creation or Re-enabling. This option specifies whether the new foreign key should be enforced for data that already exists within the table. If set to No, any data that breaks the new referential integrity rule will remain in the table. If set to Yes, the creation of the new foreign key will not be permitted if conflicting data exists. You should leave this option set to Yes.
• Name. The Name property allows you to create a unique name for the foreign key. Replace the provided name with "FK_CustomerAddresses_Customers".
• Description. The Description property allows you to add some comments to the relationship. This property does not modify the behaviour of the relationship but can be useful when other developers or database administrators are viewing the foreign key definition.
• Enforce for Replication. This option determines whether the foreign key rules should be enforced when changes to the data are made by SQL Server replication processes. Replication is beyond the scope of this tutorial. Set this option to Yes.
• Enforce Foreign Key Constraint. This option enables or disables the relationship rules for manual changes to the data. Set this option to Yes.
• INSERT and UPDATE Specification. This property contains two settings that determine the cascade options when updating and deleting information in the parent table. Set both of these options to "No Action".

To accept the settings for the new foreign key, click the Close button. Next, save the table definition and close the table designer. You may be asked to confirm the changes to the two tables. If so, click the Yes button to complete the save.

We can test the foreign key by attempting to create invalid data or by trying to delete rows from the parent table that are referenced in the child table. As a first test, try executing the following insert statement. This references a customer with a customer number of 99. As there is no such customer, the INSERT fails.

INSERT INTO CustomerAddresses
    (CustomerNumber, Address1, TownOrCity, AreaCode, Postcode)
VALUES
    (99, '1 Short Street', 'Leeds', 'WYO', 'LS1 1SL')

As a second test, we can try to delete customer number one. This customer has a related address, which would be orphaned, so the DELETE fails with an error.

Adding a Foreign Key Using Transact-SQL

Foreign keys can be added using the T-SQL ALTER TABLE command with the ADD CONSTRAINT clause. Two further items are required. The first is the FOREIGN KEY clause followed by a comma-separated list of the parent table's primary key or unique constraint columns in parentheses. The second additional item is a REFERENCES clause, which specifies the list of foreign key columns.

To create a relationship between the ContractNumber of the Jobs and Contracts table, execute the following statement:

ALTER TABLE Jobs
ADD CONSTRAINT FK_Jobs_Contracts
FOREIGN KEY (ContractNumber)
REFERENCES Contracts (ContractNumber)

JoBS Database Foreign Keys

The remainder of the foreign key constraints can now be added to the JoBS tutorial database. Use either the graphical user interface tools or T-SQL to add the following items.