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.
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:
Get Techopedia delivered to your inbox!