Foreign Key

In the context of relational databases, a foreign key is a referential constraint between two tables.

A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table. To do this, we will place a foreign key in the ORDER table and have it relate to the primary key of the CUSTOMER table.

The foreign key identifies a column or set of columns in one (referencing or child) table that refers to a column or set of columns in another (referenced or parent) table. The columns in the child table must reference the columns of the primary key or other superkey in the parent table. The values in one row of the referencing columns must occur in a single row in the parent table. Thus, a row in the child table cannot contain values that don't exist in the parent table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the child table may refer to the same row in the parent table. Most of the time, it reflects the one (parent table or referenced table) to many (child table, or referencing table) relationship.

The child and parent table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key.

A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.

Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.

2.0 Relationships to Foreign key a foreign key is an attribute in a field or column whose values match the primary key values in a related table. For example if there are two tables one of the tables will have a primary key. The primary key is a unique column within the table that does not appear twice in the same column. The second table will have both a primary key and a foreign key. The foreign key will be the primary key that is found in the first table. The second table containing a new primary key will be completely different from the first table and will be unique as well. Once we understand this concept we have what is called referential integrity. Referential integrity means that if the foreign key contains a value, that value refers to an existing valid row in another (first) relation table. If a relational database is being created there must also be referential integrity. For example if we have a column labeled ID numbers and have a list of numbers, in our second table there will also be a column with exactly the same label and exactly the same numbers just a different location.