About the SQL Server Foreign Key Clause

A foreign key is a column or group of columns in one table that contains values that match the primary key values in the same or another table. Each foreign key is defined using the FOREIGN KEY clause combined with the REFERENCES clause.

The FOREIGN KEY clause has the following form:

 

 

The FOREIGN KEY clause defines all columns explicitly that belong to the foreign key. The REFERENCES clause specifies the table name with all columns that build the corresponding primary key. The number and the data types of the columns in the FOREIGN KEY clause must match the number and the corresponding data types of columns in the REFERENCES clause (and, of course, both of these must match the number and data types of the columns in the primary key of the referenced table).

The table that contains the foreign key is called the referencing table, and the table that contains the corresponding primary key is called the parent table or referenced table. Example 1 shows the specification of the foreign key in the works_on table of the sample database.

NOTE

You have to drop theworks_ontable before you execute the following example.

EXAMPLE 1

 

The works_on table in Example 1 is specified with two declarative integrity constraints: prim_works and foreign_works. Both constraints are table-level constraints, where the former specifies the primary key and the latter the foreign key of the works_on table. Further, the constraint foreign_works specifies the employee table as the parent table and its emp_no column as the corresponding primary key of the column with the same name in the works_on table.

The FOREIGN KEY clause can be omitted if the foreign key is defined as a column-level constraint, because the column being constrained is the implicit column “list” of the foreign key, and the keyword REFERENCES is sufficient to indicate what kind of constraint this is. The maximum number of FOREIGN KEY constraints in a table is 63.

A definition of the foreign keys in tables of a database imposes the specification of another important integrity constraint: the referential integrity.