[WEBINAR] Extract Value From Your Data Through Micro-Queries

Foreign Key

Definition - 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

For any column acting as a foreign key, a corresponding value should exist in the link 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.

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.

Foreign key constraints are either immediate or deferred. By default, foreign key constraints are immediate. A violation of immediate foreign key constraints throws an exception immediately. Deferred foreign key constraints are reported only during a commit.

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.
Share this: