Tips for Using Foreign Keys

When you design the database's data model, you specify which tables are related to each other. However, when you implement the data model in SQL Server, some technical details you might not have thought about can make your implementation easier and simplify the maintenance of relationships in your database. Here are some tips and a few important reminders.

Using Explicit Constraint Names

SQL Server lets you add constraints without explicitly giving them names. When you create an unnamed constraint, SQL Server assigns it a cumbersome name that consists of the designation PK (for primary key) or FK (for foreign key), the table name, and a hexadecimal number.

For example, consider tables P and S, which Listing A creates. Notice that neither the primary keys nor the foreign key were given constraint names. To find out the constraint names that SQL Server generated for tables P and S, issue the sp_helpconstraint stored procedure on the tables. You get the following constraint names: PK__P__267ABA7A (primary key on P), PK__S__286302EC (primary key on S), and FK__S__key_col__29572725 (foreign key on S). SQL Server constructs the name of a primary key constraint as PK + two underscores + table name + two underscores + eight hex digits. The name of a foreign key constraint is FK + two underscores + secondary table name + two underscores + secondary column name + two underscores + eight hex digits. When you create indexes to enforce primary key constraints, SQL Server gives those indexes the same names as the primary keys. These awkward constraint names are the index names that show up in the execution plans when you analyze queries, and they're the names that you need to supply when you drop a constraint by using the command

ALTER TABLE DROP CONSTRAINT 

A good practice is to provide your own names for the constraints following a convention that you find convenient. The code in Listing B shows how to generate your own constraint names.

Creating a Composite Foreign Key

A foreign key doesn't necessarily have to be a single-column foreign key. If a relationship is based on multiple columns, you can create a composite foreign key by simply specifying all column names in the parentheses in the CREATE TABLE clause, separated by commas, as Listing C shows.

Disabling Constraints Instead of Dropping Them

If you have a foreign key enforcing a relationship and decide at some point to implement the relationship by using a mechanism other than a foreign key—for example, by using stored procedures or triggers—you don't have to drop the foreign key. Instead, you can disable it by using

ALTER TABLE NOCHECK CONSTRAINT 

If the foreign key didn't exist in the table initially, first create it, then disable it. This process lets visual tools such as Enterprise Manager recognize the relationships and display them. Also, when you use the sp_help or sp_helpconstraint system stored procedures to investigate the tables' schema, you'll be able to recognize the relationships between tables more easily.

Pointing a Foreign Key to a Unique Index

You probably know that a foreign key must reference a column that has a primary key or a unique constraint defined on it. However, you might not know that a foreign key can also reference a column that has a unique index. Note that this option is specific to SQL Server; the ANSI standard doesn't deal with physical mechanisms such as indexes, so nothing in the standard supports this option. Usually, settling for a unique index instead of a primary key or unique constraint is a bad practice.

Constraints have logical meanings in our data model, whereas indexes don't, and uniqueness is a logical issue. Some index options such as IGNORE_DUP_KEY and PAD_INDEX are available only when you create an explicit index; however, you can't specify those options when you create a constraint—which might be a good enough reason to choose an explicit index over a constraint. If you decide that these options are valuable for you, you have to create your own index instead of using the index that the constraint creates.

Using Abbreviated Foreign Key Constraint Syntax

When I write code for test purposes, I usually try to code as fast as I can. I use any abbreviated code format that T-SQL supplies to create my test code more quickly. Besides not supplying your own constraint names (which is a bad practice in production environments), T-SQL provides two easy abbreviations that you can use. The first abbreviation is to not specify the destination column names after the destination table name. When you don't specify the column names, as Listing D shows, SQL Server assumes that you want to point to the primary table's primary key.

You can use the second abbreviation only when you create a foreign key as part of a column's definition. Simply omit the words FOREIGN KEY, and specify only the REFERENCES clause, as Listing E shows. When you don't have a lot of time to perform a test or when you want to keep your code as short as possible for any other reason, these abbreviations can help.