In database management systems, a Foreign Key is an attribute (or set of attributes) in one table that refers to the primary key (or candidate key) of another table. It is the main mechanism in the relational model for creating and enforcing relationships between tables.
Foreign keys ensure that relationships between records remain valid and consistent, a concept known as referential integrity.
Foreign keys are widely used in relational databases to represent:
one-to-many relationships
many-to-many relationships
linked entities across tables
They are also the foundation of JOIN operations in SQL.
What is a Foreign Key?
A foreign key is:
An attribute (or group of attributes) in a child table
That references the primary key or unique key of another table
Used to establish relationships between tables
A foreign key constraint ensures that:
a child record cannot reference a non-existing parent record
relationships remain valid and consistent
Example of a Foreign Key
Consider two tables:
DEPARTMENT Table
| Dept_ID | Dept_Name | Location |
|---|---|---|
| D101 | HR | Kolkata |
| D102 | IT | Patna |
EMPLOYEE Table
| Emp_ID | Name | Dept_ID | Salary |
|---|---|---|---|
| E1 | Aman | D101 | 50000 |
| E2 | Riya | D102 | 60000 |
Here:
Dept_IDin DEPARTMENT is the primary keyDept_IDin EMPLOYEE is a foreign key referencing DEPARTMENT
This relationship ensures that every employee belongs to a valid department.
SQL Example
This constraint prevents inserting values like:
Dept_ID = 'D999'
if no such department exists in the DEPARTMENT table.
How Foreign Keys Work
Whenever data is inserted, updated, or deleted, the DBMS checks the foreign-key constraint.
Insert Check
You cannot insert a child row with a foreign-key value that does not exist in the parent table.
Delete Check
You cannot delete a parent row if child rows still reference it, unless cascading rules are defined.
Update Check
Changing referenced key values may be restricted or automatically propagated.
Referential Integrity
Foreign keys enforce referential integrity, which means:
every foreign-key value must refer to a valid parent row
relationships between tables remain logically correct
This prevents:
invalid references
inconsistent data
orphan records
Cascading Actions
DBMS supports actions that automatically handle related rows.
ON DELETE CASCADE
Deleting a parent row automatically deletes related child rows.
ON UPDATE CASCADE
Updating a parent key automatically updates matching foreign keys.
SET NULL
Foreign-key values become NULL when the parent row is removed.
RESTRICT / NO ACTION
Prevents deletion or update if dependent child rows exist.
Example of Cascading
If a department is deleted, all employees belonging to that department are automatically deleted.
Key Properties of Foreign Keys
1. Maintains Relationships
Foreign keys connect related tables together.
2. Enforces Consistency
Only valid references are allowed.
3. Supports Joins
Foreign keys are commonly used in SQL JOIN operations.
4. Can Reference Primary or Unique Keys
The referenced column is usually:
a primary key
or a unique key
5. Multiple Foreign Keys Allowed
A table may contain several foreign keys referencing different tables.
Why Foreign Keys Matter?
Foreign keys are important because they:
enforce referential integrity
prevent orphan records
maintain consistency between tables
support normalized relational design
make relationships explicit and easy to query
Without foreign keys, relational databases would lose much of their structural reliability.
Foreign Key vs Primary Key
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identifies rows | References rows in another table |
| Uniqueness | Must be unique | Can contain duplicates |
| NULL Allowed | No | Usually yes (unless restricted) |
| Table Role | Parent identifier | Relationship connector |
| Count per Table | One primary key | Multiple foreign keys possible |
Summary
A Foreign Key in DBMS is an attribute (or set of attributes) in one table that references the primary key of another table. It is used to establish relationships between tables and enforce referential integrity, ensuring that related records remain valid and consistent. Foreign keys are one of the core concepts of relational database design and are essential for building connected, reliable database systems.