A foreign key is a column (or group of columns) within a table that refers or relates to a unique value in a referenced table. Each value in the foreign key column must have a matching value in the referenced table.

To ensure that each record in the referencing table references exactly one record in the referenced table, the referenced columns in the referenced table must have a primary key constraint or have both unique and not-null constraints. Having a unique index is not sufficient.

For example, in a banking environment, one table might hold customer information (customers), and another table might hold account information (accounts). Each account is related to a particular customer, and each customer is identified with a unique ID (CUST_ID). Some customers can have more than one account. The CUST_ID can then serve as the primary key of the customers table. The accounts table also contains the CUST_ID information to identify which customer owns a particular account; this makes it possible to look up customer information based on account information. The copy of the CUST_ID in the accounts table is a foreign key; it references the matching value in the primary key of the customers table.

In this example, the referencing table accounts can be created with the following statement:

In the REFERENCES clause, only the referenced table is specified, with no referenced column. By default, the primary key is assumed.

In the previous example, the primary key and foreign key use a single column. However, primary and foreign keys can be composed of more than one column. Since each foreign key value must exactly match the corresponding primary key value, the foreign key must contain the same number and data type of columns as the primary key, and these key columns must be in the same order.

However, a foreign key can have different column names than the primary key and a different default value. Although, since values in the referenced table must be unique, default values are rarely used for columns that are part of a primary key or a foreign key.

Although primary key values must be unique, foreign key values are not required to be unique. For example, a single customer at a bank might have multiple accounts. The customer ID that appears in the primary key column (CUST_ID) in the customers table must be unique; however, the same customer ID might occur multiple times in the foreign key column (CUST_ID) in the accounts table. As shown in the diagram, customer SMITH has two accounts, and therefore the customer ID for SMITH appears twice in the foreign key column of the accounts table.

In some cases, a foreign key in a table can refer to a primary key in the same table – in such a case, the same table is the referenced table and the referencing table. For example, in a table of employees, each employee record might have a field (MGR_ID) that contains the ID of the manager of that employee. The managers themselves might be stored in the same table. Thus the MGR_ID column in that table can be a foreign key that refers to the employee ID (EMP_ID) column of that same table, as shown in the following diagram.

In this example, Rama reports to Smith (MGR_ID for Rama is 20, and EMP_ID for Smith is 20). Smith reports to Annan (MGR_ID for Smith is 1, and EMP_ID for Annan is 1.) If Annan is the president of the company, they do not have a manager, and the value of the foreign key (MGR_ID) is NULL.

If a primary key is composed of more than one column, the primary key should be defined after the columns. For example:

A similar syntax can be used for foreign keys. However, foreign keys should always be defined with the CONSTRAINT syntax that also includes a constraint name. If you have defined a constraint name, you can remove the constraint dynamically with ALTER TABLE statements after tables have been created.

Example of creating a table with a CONSTRAINT name (emp_fk1):

Defining a foreign key always creates an index on the foreign key column or columns. Each time that a referenced record is updated or deleted, the server checks that there are no referencing records that are left without a reference. Foreign key indexes improve the performance of foreign key checking.