Linking Relational Database Tables

There are two special keys in a relational database. As we saw earlier, a primary key uniquely identifies a record in a table and another type of key, a foreign key establishes a link between tables.
The graphic below illustrates both key types.

The primary key of the Suppliers tables is SuppID, and the primary key of the Products table is ProdId. When the SuppID is inserted into the Products table, it becomes a foreign key in the Products table because it is used to link the Suppliers and Products tables. This means, that for every foreign key^[1] in a database, there must be a corresponding primary key. You will learn about the importance of keys in establishing data integrity^[2] within a relational database.

The one area of difficulty with using brief column names is the occasional appearance of a foreign key in a table in which another column has the same name that the foreign key column has in its parent table. One possible long-term solution is to allow the use of the full foreign key name, including the table name of its parent table, as a column name in the local table (such as BOOKSHELF.Title as a column name). The practical need to solve the same-name column problem requires one of the following actions:

1. Invent a name that incorporates the source table of the foreign key in its name without using the dot (using an underscore, for instance).
2. Invent a name that incorporates an abbreviation of the source table of the foreign key in its name.
3. Invent a name different from its name in its source table.
4. Change the name of the conflicting column.

None of these is particularly attractive, but if you come across the same-name dilemma, you will need to take one of these actions.

[1]foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.

[2]data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist. Problems with data integrity occur when a value in one table that is supposed to relate to a value in another cannot because the second value either has been deleted or was never entered.