SQL Foreign Key

SQL FOREIGN KEY Constraint

A FOREIGN KEY is used to create a relationship between two tables. It ensures that a value in one table corresponds to a valid value in another table.

Why Use FOREIGN KEY?

• Maintain referential integrity
• Prevent orphan records
• Enforce valid relationships between tables

FOREIGN KEY in CREATE TABLE

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Named FOREIGN KEY Constraint

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(id)
);

ON DELETE and ON UPDATE

You can define actions when the referenced row changes.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id)
  REFERENCES customers(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

• CASCADE – automatically update/delete related rows
• RESTRICT – prevent deletion/update if related rows exist
• SET NULL – set foreign key column to NULL (must allow NULL)
• NO ACTION – similar to RESTRICT (DB-dependent behavior)

Add FOREIGN KEY with ALTER TABLE

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id);

Drop FOREIGN KEY (MySQL / MariaDB)

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

Index Requirement

Foreign key columns should be indexed for performance. In many systems, an index is automatically created when defining a foreign key.

Common Mistakes

• Referencing a column that is not PRIMARY KEY or UNIQUE
• Mismatched data types between parent and child columns
• Forgetting ON DELETE behavior

Best Practice

Always define foreign keys for related tables to maintain data integrity. Carefully choose ON DELETE and ON UPDATE actions based on business rules.

Next Step

Continue with SQL CHECK to enforce value conditions inside a table.