FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to create a link between two tables. It ensures that the value in one table (child) matches a value in another table (parent), enforcing referential integrity.

A foreign key in one table refers to the primary key in another table.

Syntax

CREATE TABLE child_table (
  column_name datatype,
  FOREIGN KEY (column_name) REFERENCES parent_table(primary_key_column)
);

Or to add it to an existing table:

ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (column_name)
REFERENCES parent_table(primary_key_column);

Example: Link employees with departments

We’ll create a departments table and then reference it from the employees table using a FOREIGN KEY.

CREATE TABLE departments (
  dept_name VARCHAR(50) PRIMARY KEY
);

INSERT INTO departments (dept_name) VALUES
('HR'), ('IT'), ('Sales'), ('Marketing'), ('Finance');

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50),
  gender VARCHAR(50),
  FOREIGN KEY (department) REFERENCES departments(dept_name)
);

Now if someone tries to insert an employee into a non-existent department:

INSERT INTO employees (id, name, age, department, gender)
VALUES (5, 'Sam', 29, 'Legal', 'male'); -- ❌ Error!

This insert fails because "Legal" is not a department listed in the departments table.

Key Points

  • Maintains relationships between tables
  • Prevents invalid or orphaned references
  • Parent table must have a PRIMARY KEY or UNIQUE on the referenced column

Next, we’ll explore DEFAULT values — used to assign automatic values to columns when none are provided.