Create a Relationship - T-SQL Code Examples

Various code examples for using Transact-SQL (T-SQL) to create a relationship between two tables.

Basic Relationship

The following code creates two tables called Artists and Albums in the Music database. It also creates a relationship between the two tables (this is the line that goes REFERENCES Artists(ArtistId)). In this case, the Album.ArtistId column becomes a foreign key to the Artists.ArtistId column (which is the primary key of its table).

The FOREIGN KEY Clause

You can also use the FOREIGN KEY clause to explicitly state that the column should be a foreign key column. To do this, simply place FOREIGN KEY at the start of your relationship definition.

Therefore, the previous example could be modified to this:

The CONSTRAINT Clause

Foreign key constraints can also be created using the CONSTRAINT clause. This creates a table constraint.

Multicolumn Key Constraints

Multicolumn key constraints are created using the CONSTRAINT clause, as you can provide multiple column names separated by a comma.

This example assumes that both tables have an ArtistId and ArtistName column, and that both columns are the primary key of the Artists table (and therefore, both Albums.ArtistId and Albums.ArtistName columns are also the foreign key in the Albums table).

Handling Deleted Data in a Relationship

Whenever you have a relationship between two tables, you have to decide what should happen when a user tries to delete or update data in the parent table (i.e. the one with the primary key).

Here are examples for handling that situation.

NO ACTION

If a primary key row is deleted or updated, no action is taken in any foreign key column that references that row.

So using our example, if an Artists.ArtistId is deleted or updated, any corresponding Albums.ArtistId will remain unchanged.

CASCADE

If a primary key row is deleted or updated, the same action cascades down to any foreign key column that references that row.

So using our example, if an Artists.ArtistId is deleted or updated, any corresponding Albums.ArtistId will also be deleted or updated.

SET NULL

If a primary key row is deleted or updated, any foreign key column that references that row will be set to NULL.

So using our example, if an Artists.ArtistId is deleted or updated, any corresponding Albums.ArtistId will be set to NULL.

Combinations

Of course, deletions and updates don't have to be treated the same. You can set different outcomes depending on whether a row is deleted or updated. For example you could do this:

Add a Foreign Key to an Existing Table

Use the ALTER TABLE statement to create a foreign key constraint in an existing table.