How to Add a Foreign Key in SQL

Use a foreign key when one table’s column should reference a valid row in another table. This enforces relational integrity and keeps joins, deletes, and updates reliable.

What you’ll build or solve

You’ll learn how to add a foreign key in SQL during table creation and on existing tables. You’ll also know how to choose the correct parent key.

When this approach works best

This approach is the right choice when tables represent related entities.

Common real-world scenarios include:

• Orders linked to users
• Products linked to categories
• Comments linked to posts
• Enrollments linked to students
• Payments linked to invoices

This is a bad idea when the relationship is temporary, denormalized, or intentionally schema-free.

Prerequisites

You only need:

• Two related tables
• A parent table with a primary key
• Matching data types between both columns

Step-by-step instructions

Step 1: Add the foreign key constraint

The cleanest option is adding it during table creation.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

This ensures every user_id in orders exists in users.

You can also add it later.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id);

What to look for:

• Child column references parent primary key
• Data types must match
• Great for relational integrity
• Prevents orphan rows
• Supports safer joins and cascades

Examples you can copy

Products to categories

FOREIGN KEY (category_id) REFERENCES categories(category_id)

Comments to posts

FOREIGN KEY (post_id) REFERENCES posts(post_id)

Payments to invoices

FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id)

Common mistakes and how to fix them

Mistake 1: Referencing a non-unique parent column

What the reader might do:

REFERENCES users(email)

Why it breaks: the parent column may not be uniquely constrained.

Corrected approach:

Reference the primary key or a unique key.

Mistake 2: Mismatched data types

What the reader might do:

Child column VARCHAR, parent key INT.

Why it breaks: the constraint may fail or create invalid comparisons.

Corrected approach:

Use identical compatible types.

Mistake 3: Adding the key before cleaning orphan data

What the reader might do:

ALTER TABLE orders ...

Why it breaks: existing user_id values may reference missing users.

Corrected approach:

Find and fix orphan rows first.

Troubleshooting

If the constraint fails, check for orphan child rows.

If the types mismatch, align both columns.

If deletes should cascade, add ON DELETE CASCADE.

If the parent key changes, use stable surrogate IDs.

Quick recap

• Foreign keys enforce valid table relationships
• Reference parent primary or unique keys
• Match data types exactly
• Prevent orphan rows
• Add cascades when needed