Foreign Key Definition

A foreign key is a column or columns of data in one table that connects to the primary key data in the original table.

To ensure the links between foreign key and primary key tables aren’t broken, foreign key constraints can be created to prevent actions that would damage the links between tables and prevent erroneous data from being added to the foreign key column.

Differences between primary and foreign keys

A primary key in the original table, or parent table, can be targeted by multiple foreign keys from other “child” tables. But a primary key does not necessarily have to be the target of any foreign keys. A primary key is a column or a set of columns that identify a row in a table. A foreign key, however, is in a table that is different from the table that the primary key must match.

Administrators can select or change a primary key in a relational database, such as SQL Server, as needed. For example, people in a town might be uniquely identified by their driver’s license numbers in one application, but in another situation it might be more convenient to identify them according to their telephone numbers. When a primary key in a table changes, the set of associated foreign keys changes as a result.

Types of constraints

Foreign key constraints prevent invalid data from being placed into the foreign key column, because it must be one of the values contained in the table it is directed to. Database administrators can create foreign key relationships in the newest model of SQL Server 2017, by using SQL Server Management Studio or Transact-SQL. Foreign keys do not have to be linked specifically to a primary key constraint in another table; they can also reference the columns of a UNIQUE constraint elsewhere.

The different constraints include UNIQUE, CHECK and DEFAULT. UNIQUE constraints ensure that there are no duplicate values entered in specific columns that are not within a primary key. CHECK constraints limit the values that are accepted by more than one column. DEFAULT constraints apply when a null value isn’t appropriate, so administrators can set zero as a default numeric column, for instance.

Foreign key problems

Many database users encounter foreign key errors, often due to referential integrity problems. A foreign key might point to data that no longer exists, or the foreign key’s data type doesn’t match the primary key data type, eroding referential integrity.

This also occurs if the foreign key doesn’t reference all the data from the primary key. If there’s a parent table for Sales which consists of a primary key of the company name, department name and address, then the child table for Customers must refer to all attributes of the parent table — not just one or two.