Foreign Key in DBMS – Full Explanation with Example

A Foreign Key is a very important concept in relational databases. It is used to create a relationship between two tables.

What is a Foreign Key?

A Foreign Key is a column or a set of columns in one table that refers to the Primary Key in another table.

In simple words:
A Foreign Key in Table A points to a Primary Key in Table B.
It ensures that the value in Table A must exist in Table B.

Key Characteristics of a Foreign Key

1. Referential Integrity: Ensures that a record in the child table must refer to an existing record in the parent table.
2. Duplicates Allowed: A foreign key column can have duplicate values.
3. NULL Values: Foreign key columns can contain NULLs (if allowed).
4. Multiple Foreign Keys: A table can have multiple foreign keys.
5. No Automatic Indexing: Unlike a primary key, a foreign key does not create an index automatically (though you can create one manually).

Example of Foreign Key

Suppose we have two tables:

1. Departments Table (Parent Table)

(dept_id is the Primary Key)

2. Employees Table (Child Table)

In this table:

• dept_id in Employees is a Foreign Key.
• It references dept_id in the Departments table.
• It ensures that any department assigned to an employee must exist in the Departments table.

SQL Syntax to Define a Foreign Key

While creating a table:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Or, separately after table creation:

ALTER TABLE Employees
ADD CONSTRAINT fk_department
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id);

Important Rules About Foreign Keys

• You cannot insert a value into the child table that doesn’t exist in the parent table.
• If you delete a record from the parent table that is referenced by the child table, it will cause an error unless handled properly using ON DELETE CASCADE or ON DELETE SET NULL.

ON DELETE and ON UPDATE Options

You can define actions to take when the referenced primary key changes or is deleted:

Example:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

Primary Key vs Foreign Key

Real-Life Example

Imagine:

• Departments: HR, Finance, IT
• Employees: Assigned to these departments

If a new employee is entered into the Employees table with a non-existing department ID like 99, the database will reject the insertion because no such department exists — this protects data consistency.

Summary

• A Foreign Key is used to link two tables.
• It enforces referential integrity between records.
• The foreign key value must either match a value in the referenced table’s primary key or be NULL.
• Helps prevent orphan records (records that reference non-existing entities).