When you buy through affiliate links in our content, we may earn a commission at no extra cost to you. Learn What Does Foreign Key Mean?
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
The majority of tables in a relational database system adhere to the foreign key concept. In complex databases and data warehouses, data in a domain must be added across multiple tables, thus maintaining a relationship between them. The concept of referential integrity is derived from foreign key theory.
Foreign keys and their implementation are more complex than primary keys.
Techopedia Explains Foreign Key
While a primary key may exist on its own, a foreign key must always reference to a primary key somewhere. The original table containing the primary key is the parent table (also known as referenced table). This key can be referenced by multiple foreign keys from other tables, known as “child” tables.
For any column acting as a foreign key, a corresponding value should exist in the linked table. Special care must be taken while inserting data and removing data from the foreign key column, as a careless deletion or insertion might destroy the relationship between the two tables.
If the integrity between the two databases is compromised, errors may ensue.
For instance, if there are two tables, customer and order, a relationship can be created between them by introducing a foreign key into the order table that refers to the CUSTOMER_ID in the customer table. The CUSTOMER_ID column exists in both customer and order tables.
The CUSTOMER_ID in the order table becomes the foreign key, referring to the primary key in the customer table. To insert an entry into the order table, the foreign key constraint must be satisfied.
An attempt to enter a CUSTOMER_ID that is not present in the customer table fails, thus maintaining the table’s referential integrity.
In real-world databases, integrity between foreign key and primary key tables can be ensured by enforcing referential constraints whenever rows in a referenced table are updated or deleted.
Some referential actions associated with a foreign key action include the following:
Cascade
When rows in the parent table are deleted, the matching foreign key columns in the child table are also deleted, creating a cascading delete.
Set Null
When a referenced row in the parent table is deleted or updated, the foreign key values in the referencing row are set to null to maintain the referential integrity.
Triggers
Referential actions are normally implemented as triggers. In many ways foreign key actions are similar to user-defined triggers. To ensure proper execution, ordered referential actions are sometimes replaced with their equivalent user-defined triggers.
Set Default
This referential action is similar to “set null.” The foreign key values in the child table are set to the default column value when the referenced row in the parent table is deleted or updated.
Restrict
This is the normal referential action associated with a foreign key. A value in the parent table cannot be deleted or updated as long as it is referred to by a foreign key in another table.
No Action
This referential action is similar in function to the “restrict” action except that a no-action check is performed only after trying to alter the table.
