Foreign Keys

• Definitions
• Things to Remember About Foreign&Nbsp;Keys

Definitions

(Benynon-Davies 2004:583)

Foreign Keys

Foreign keys are used to link rows of database tables to show some kind of relationship between them.

(Carter 1995:79)

Referential Integrity

The referential integrity rule states that:

The database must not contain any unmatched foreign key values.

This means that a non-null foreign key value must have its counterpart in the tuple to which it refers. That is, this value of the foreign key must appear as one of the values of the primary key.

(Carter 1995:92)

(Connolly & Begg 2005:79)

Edmondson

A set of attributes FK in a relation schema R1 is a foreign key of R1 that references R2 if it satisfies the following rules:

1. The attributes in FK have the same domains(s) as the primary key attributes PK of R2; the attributes in FK are said to reference or refer to R2.

2. A value of FK is a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 the current state r2(R2) or is NULL. In the former case, we have t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2.

(Elmasri & Navathe 2007:158)

foreign key: A column or combination of columns that is the same as the primary key of a table in the same database.

(Harrington 2002:380)

foreign key   A column of a table that must either be null or must contain the value of a primary key of some table.

(Larson 1995:245)

A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Tech on the Net

A foreign key (FK) is a field or group of fields in a database record that points to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization. foreign keys that refer back to the same table are called recursive foreign keys.

For example, suppose an accounts database has a table of invoices, and each invoice is associated with a particular supplier. Supplier details (such as address or phone number) are kept in a separate table; each supplier is given a 'supplier number' to identify them. Each invoice record has a field containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table, and a foreign key in the Invoices table.

A record may contain more than one foreign key: in our example an invoice may also be associated with a particular department, project etc. foreign key fields would then link the invoice record to records in Department or Project tables.

The use of a foreign key often assumes its existence as a primary key somewhere else. Improper foreign key/primary key relationships are the source of many database problems (see referential integrity).

A foreign key constraint is a constraint that data which serves as a foreign key in one database record cannot be removed as there is still data in another record that assumes its existence.

Wikipedia

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used would be defined by the definition of the referential integrity constraint.

Wikipedia

[Top]

Things to Remember About Foreign Keys

A foreign key in a relation is not necessarily (a part of) the primary key of the relation.

Despite what some textbooks and websites say:

• The primary key referenced by a foreign key may be in the same relation as the foreign key.

• A foreign key may be null.

  If you want to prevent a particular foreign key from ever being null, you must use the  NOT NULL  constraint as well as the  FOREIGN KEY  constraint.

[Top]