What is a Foreign Key?

Databases are composed of tables in which data, or information, is stored. A foreign key is a mechanism in a relational database that allows for the association of those tables, or for the tables to have a relationship with one another. There are unique characteristics that a foreign key must have. It must be a column or a group of columns in a table whose values establish a relationship with values in another table within the same database.

A foreign key guarantees that rows in one table correspond to rows in some other table, thereby establishing database-wide relationships, or references. The table containing the foreign key is the "child," and the other table is the "parent." It is possible for the value of a foreign key to be null or empty and for it to actually reference or relate to what is known as the primary key of the table in which it is found. This is known as self-referencing.

One must have a good understanding of what a primary key is before attempting to fully comprehend the concept behind foreign keys. For example, if there are two tables in a database storing information about published books for a website that sells books, one table might be the publishers table and the other the books table. The publishers table could consist of two columns, a primary key that is always unique for every record and a publisher name column. A minimum of three columns would make up the books table, holding the primary key or unique identifier for each book, the titles of books and a column via which a relationship would be established with the publishers table. That column would be the foreign key.

The primary key for the publishers table could be something like "pub_id" with values of: P01, P02, P03, etc. A relationship would be established with the books table if it contained a pub_id column with the same values. This would be the foreign key that would relate this child table back to its parent — the publishers table.

A foreign key can, however, have a different column name than the parent key that it references. Sound understanding of the workings of primary and foreign keys are essential in maintaining referential integrity. Very large databases that are composed of many tables or that involve a junction table present more than one type of relationship, which can complicate the task of working with foreign keys.