MySQL FOREIGN KEY Constraint

MySQL Tutorial

MySQL FOREIGN KEY Constraint 

The FOREIGN KEY constraint in MySQL is used to maintain referential integrity between two tables by creating a parent–child relationship.

  •  It ensures that data in one table matches valid data in another table.

 What is a FOREIGN KEY?

  • A foreign key is a column (or group of columns) in a child table

  • It references the PRIMARY KEY or UNIQUE key of a parent table

  • Prevents invalid (orphan) records

Rule:
You cannot insert a value in the child table that does not exist in the parent table.

 Why Use FOREIGN KEY?

  •  Maintains data consistency
  • Prevents invalid references
  •  Enforces relationships between tables
  •  Very important in real-world databases

 Basic Syntax

 Simple Example

Parent Table

Child Table

  • employees.dept_id must exist in departments.dept_id

 Inserting Data (Valid & Invalid)

Valid Insert

Invalid Insert

  •  Error: foreign key constraint fails (dept_id = 5 does not exist)

 FOREIGN KEY with ON DELETE & ON UPDATE

A FOREIGN KEY creates a relationship between a child table and a parent table. It ensures that data in the child table always refers to valid data in the parent table. ON DELETE defines what happens to child rows when a parent row is deleted. ON UPDATE defines what happens to child rows when a parent key is updated. In MySQL, foreign keys work only with the InnoDB storage engine.

Parent Table Example

Child Table with FOREIGN KEY

ON DELETE CASCADE Example

Deleting a parent row automatically deletes related child rows.

  • All orders with customer_id = 1 are deleted automatically.

ON DELETE SET NULL Example

  •  Orders remain, but customer_id becomes NULL.

ON DELETE RESTRICT / NO ACTION Example

  •  Delete fails if related orders exist.

UPDATE CASCADE Example

  •  All matching orders.customer_id values update automatically.

ON UPDATE SET NULL Example

  •  Child table customer_id becomes NULL.

 Complete Example with CASCADE

 Adding FOREIGN KEY Using ALTER TABLE

 Dropping FOREIGN KEY

  •  Constraint name is required

 FOREIGN KEY vs PRIMARY KEY (Interview)

FeaturePRIMARY KEYFOREIGN KEY
Uniqueness Yes No
NULL allowed No Yes
TableParentChild
PurposeIdentify rowLink tables

 Important Rules (Exam)

  • Parent column must be PRIMARY KEY or UNIQUE

  • Data types must match

  • Engine must be InnoDB

  • Child value must exist in parent table

 Common Mistakes

  •  Using MyISAM engine
  •  Mismatched data types
  •  Forgetting parent table first
  •  Dropping parent table before child

Interview Questions & MCQs (Very Important)

Q1. What is FOREIGN KEY used for?

A) Speed queries
B) Uniqueness
C) Referential integrity
D) Indexing

Answer: C

Q2. FOREIGN KEY references which key?

A) INDEX
B) PRIMARY or UNIQUE
C) NOT NULL
D) CHECK

Answer: B

Q3. Which engine supports FOREIGN KEY in MySQL?

A) MyISAM
B) MEMORY
C) CSV
D) InnoDB

Answer: D

Q4. What does ON DELETE CASCADE do?

A) Prevent delete
B) Set NULL
C) Delete child rows
D) Ignore delete

Answer: C

Q5. Can a FOREIGN KEY contain NULL?

A) Yes
B) No

Answer: A

Q6. How many FOREIGN KEYS can a table have?

A) One
B) Two
C) Unlimited
D) None

Answer: C

Q7. What happens if parent row is deleted without CASCADE?

A) Child deleted
B) Error occurs
C) Child updated
D) Nothing

Answer: B

 Real-Life Use Cases

  •  Customers & Orders
  •  Departments & Employees
  •  Users & Payments
  •  Students & Courses
  •  Products & Categories

 Summary

  • FOREIGN KEY enforces table relationships

  • Prevents invalid references

  • Supports CASCADE actions

  • Requires InnoDB

  • Very important for SQL exams & interviews

You may also like...