A foreign key is a column (or group of columns) within a table that refers to (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 column(s) 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.

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 the above 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. This is a preferred way that helps avoiding errors while specifying the referenced columns.

In the above 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.

A foreign key can also have different column names than the primary key. The foreign key and primary key can also have different default values. However, since values in the referenced table must be unique, default values are not much used and are rarely used for columns that are part of a primary key. Default values are also not used very often for foreign key columns.

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 account ID (ACCT_ID) that appears in the primary key column in the Customers table must be unique; however, the same CUST_ID might occur multiple times in the foreign key column in the ACCOUNTS table. As you can see in the illustration above, customer SMITH has more than one account, and therefore her CUST_ID appears more than once 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 that contains the ID of the manager (MGR_ID) of that employee. The managers themselves might be stored in the same table. Thus the MGR_ID of that table can be a foreign key that refers to the employee ID (EMP_ID) of that same table. The following diagram illustrates this.

In this example, Rama’s manager is Smith (Rama’s MGR_ID is 20, and Smith’s EMP_ID is 20). Smith reports to Annan (Smith’s MGR_ID is 1, and Annan’s EMP_ID is 1.) Jones’s manager is Wong, and Wong’s manager is Annan. If Annan is the president of the company, she does 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, it should be defined after the columns. For example:

A similar syntax may 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(s). Each time when 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.