Can a foreign key be NULL, and can it reference the same table?
SHORT ANSWER
Yes to both. A nullable foreign key means the relationship is optional for that row. A foreign key can also reference the same table's primary key, which is how hierarchies like employee and manager are stored.
Explain it simply
A student's "class representative" column may be blank for students who have not chosen one, and a representative is just another student in the same table. Blank is allowed because no reference means no rule to check.
Example
sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT NULL,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
INSERT INTO employees VALUES (1, 'Kavita', NULL); -- CEO has no manager
INSERT INTO employees VALUES (2, 'Rohan', 1); -- valid
INSERT INTO employees VALUES (3, 'Aman', 99); -- fails, no employee 99Points to remember:
- A foreign key value, if not NULL, must match an existing parent key value.
- NULL skips the check. If every row must have a parent, declare the column NOT NULL.
- For a self-referencing table, insert the root row first, with NULL, then rows pointing to it.
- A foreign key may point to the primary key or any UNIQUE column of the parent.
- With composite foreign keys, if any part is NULL the check is usually skipped (MATCH SIMPLE behaviour).
What to say in the interview
"A foreign key can be NULL when the relationship is optional, and it can reference its own table, like manager_id pointing to id. Non-NULL values must match a real parent row."
Common mistakes
- Thinking a foreign key must always have a value.