A foreign key is a column or set of columns in a relational database table that exist as the primary key in another table and provides a link between data in these two tables. That means a foreign key in one table references the primary key of another table for establishing a link between them.

Let’s, for example, consider a relational database with two tables: a CUSTOMER table and an ORDER table. Each order in the ORDER table must refer to a single customer in the CUSTOMER table. To reflect this concept in the database, a foreign key column (e.g. CUSTOMERID) is introduced to the ORDER table. This foreign key references the primary key of CUSTOMER table. (e.g. CUSTOMERID). This is because CUSTOMERID only contains values from that primary key field. CUSTOMERID from ORDER table will identify the particular customer of CUSTOMER table which placed the order.

Foreign key in SQL

In the preceding discussion a foreign key column (e.g. CUSTOMERID) is introduced to the ORDER table. Since this foreign key references the primary key of CUSTOMER table, the name of the constraint is ORDER_CUSTOMER_FK . To create ORDER table in SQL this foreign key constraint is used in the CUSTOMERID column of the ORDER table. This example is in SQL as follows.

The features of foreign key

• A foreign key is a field that references a candidate key.
• A table may have multiple foreign keys.
• Each foreign key in a database table can have a different parent table.
• Any value in the foreign key field of a child table should match a value from the primary key field in the other parent table.
• The matching of data from foreign key values to primary key values represents data relationships in a relational database.
• If the relation between two tables is one-to-one, it is perfectly fine to use a foreign key as the primary key. But if the relation between two tables is one-to-many, foreign keys need to allow non-unique values. 
• Foreign keys can contain duplicate values.

• A foreign key can contain null value but containing null values, a foreign key cannot match the values of a parent primary key.
• Foreign keys do not uniquely identify records in their own tables.
• Foreign key identifies a particular row of the referenced table.

The purpose of foreign key in database

• A foreign key identifies a particular row of referenced table.
• Foreign key ensures referential integrity of the data.
• A foreign key also ensures that a row in a primary key table cannot be deleted or updated if it’s referenced by other tables and therefore foreign key guarantees data integrity.
• Foreign key is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table.

Related posts :