FOREIGN KEY Constraint

What is a FOREIGN KEY?

A FOREIGN KEY links a column in one table to the primary key of another table. It ensures that values in the child table always reference a valid row in the parent table, maintaining referential integrity.

Syntax

CREATE TABLE enrollments (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id)
);

With explicit FOREIGN KEY syntax and actions:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE
    ON UPDATE SET NULL
);

When to Use

• Enforcing that an order references a valid product
• Ensuring enrollments reference existing students and courses
• Preventing orphan rows in child tables

Key Points

1. Referential Integrity — The foreign key value must exist in the referenced table or be NULL.
2. ON DELETE CASCADE — Automatically deletes child rows when the parent row is deleted.
3. ON DELETE SET NULL — Sets the foreign key column to NULL when the parent is deleted.
4. ON DELETE RESTRICT — Prevents deleting the parent row if child rows exist (default behavior).
5. Performance — Index foreign key columns for faster join and constraint-check performance.