A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table’s primary key values to the other table. This column becomes a foreign key in the second table.
You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table.
For example, the titles table in the pubs database has a link to the publishers table because there is a logical relationship between books and publishers. The pub_id column in the titles table matches the primary key column of the publishers table. The pub_id column in the titles table is the foreign key to the publishers table.
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, then verification of the FOREIGN KEY constraint will be skipped. When a new row of data is added to a table or data in an existing row is changed, values in the foreign key column(s) must either exist in the primary key column(s) of the other table or be NULL.
Note A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table (self-referencing tables). An example of self-referencing is an employee table that contains three columns: employee_number, employee_name, and manager_employee_number. Because the manager is an employee too, there is a foreign key relationship from the manager_employee_number column to the employee_number column.
Although the primary purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a publisher is deleted from the publishers table, and the publisher’s ID is used for books in the titles table, the relational integrity between the two tables is broken; the deleted publisher’s books are orphaned in the titles table without a link to the data in the publishers table. A FOREIGN KEY constraint prevents this situation. It enforces referential integrity by ensuring that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail if the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To change or delete a row in a FOREIGN KEY constraint successfully, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, thereby linking the foreign key to different primary key data.
A FOREIGN KEY constraint is a candidate for an index for two reasons:
| Creating and Modifying FOREIGN KEY Constraints | Indexes |