ASA SQL User's Guide
Ensuring Data Integrity
Enforcing entity and referential integrity
A foreign key (made up of a particular column or combination of columns) relates the information in one table (the foreign table) to information in another (referenced or primary) table. For the foreign key relationship to be valid, the entries in the foreign key must correspond to the primary key values of a row in the referenced table. Occasionally, some other unique column combination may be referenced instead of a primary key.
The sample database contains an employee table and a department table. The primary key for the employee table is the employee ID, and the primary key for the department table is the department ID. In the employee table, the department ID is called a foreign key for the department table because each department ID in the employee table corresponds exactly to a department ID in the department table.
The foreign key relationship is a many-to-one relationship. Several entries in the employee table have the same department ID entry, but the department ID is the primary key for the department table, and so is unique. If a foreign key could reference a column in the department table containing duplicate entries, or entries with a NULL value, there would be no way of knowing which row in the department table is the appropriate reference. This is a mandatory foreign key.
Suppose the database also contained an office table listing office locations. The employee table might have a foreign key for the office table that indicates which city the employee's office is in. The database designer can choose to leave an office location unassigned at the time the employee is hired, for example, either because they haven't been assigned to an office yet, or because they don't work out of an office. In this case, the foreign key can allow NULL values, and is optional.