1
ON DELETE CASCADE → When a parent row is deleted, all related child rows are automatically deleted.
2
ON DELETE SET NULL → When a parent row is deleted, the foreign key column in the child rows is set to NULL.
3
Requirement: SET NULL requires the foreign key column to allow NULL values.
4
Usage: CASCADE is used when child data should not exist without its parent; SET NULL is used when child records should remain but lose the relationship.

Example

2. What Is a Super Key?

1
A super key is any column or combination of columns that can uniquely identify a row in a table.
2
It may contain extra, unnecessary attributes.
3
All candidate keys are super keys, but not all super keys are candidate keys.
4
A super key becomes a candidate key only when it has no unnecessary columns.

Example of Super Key vs Candidate Key

1
Columns: (emp_id), (email), (emp_id, name)
2
(emp_id) → Candidate Key (minimal)
3
(email) → Candidate Key (minimal)
4
(emp_id, name) → Super Key but not a candidate key because it has extra attributes.

3. Can a Foreign Key Reference a Non-Primary Key?

1
Yes — a foreign key can reference any column in another table as long as that column is indexed with either:
2
• a PRIMARY KEY constraint, or
3
• a UNIQUE constraint.
4
The referenced column must have unique values so MySQL can enforce referential integrity.

Example: Foreign Key Referring to a UNIQUE Key

Foreign keys do not need to reference primary keys — they only need to reference a uniquely indexed column.