Advanced Key Concepts and Foreign Key Behaviors
ON DELETE CASCADE → When a parent row is deleted, all related child rows are automatically deleted.
ON DELETE SET NULL → When a parent row is deleted, the foreign key column in the child rows is set to NULL.
Requirement: SET NULL requires the foreign key column to allow NULL values.
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.
A super key is any column or combination of columns that can uniquely identify a row in a table.
It may contain extra, unnecessary attributes.
All candidate keys are super keys, but not all super keys are candidate keys.
A super key becomes a candidate key only when it has no unnecessary columns.
Columns: (emp_id), (email), (emp_id, name)
(emp_id) → Candidate Key (minimal)
(email) → Candidate Key (minimal)
(emp_id, name) → Super Key but not a candidate key because it has extra attributes.
Yes — a foreign key can reference any column in another table as long as that column is indexed with either:
• a PRIMARY KEY constraint, or
• a UNIQUE constraint.
The referenced column must have unique values so MySQL can enforce referential integrity.
Foreign keys do not need to reference primary keys — they only need to reference a uniquely indexed column.