SQL 2005+

SQL Server Foreign Keys

by Richard Carr, published at http://www.blackwasp.co.uk/SQLForeignKeys.aspx

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.

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.

DELETE FROM Customers WHERE CustomerNumber = 1