Why foreign keys are allowed to have NULL values? Explain with an example.

Exercises · Q2

Q. Why foreign keys are allowed to have NULL values? Explain with an example.

Puducherry TnboardTextbookSubjective· 3mImportance★★★★★

10% · 3/29 Questions

✓ Free question

Concept understanding — Foreign Key Nullability

Imagine you are the registrar at a college. Every student belongs to a department — say, Physics or Economics. You keep a list of students, and next to each student’s name you write their department code. That department code is a foreign key: it points to the official list of departments. Now, a question arises: what happens if a student has not yet been assigned to any department? Do you leave that slot blank, or do you force them to pick one?

That blankness is the heart of foreign key nullability.

Foreign key nullability simply means: can a foreign key column contain a NULL value? In database terms, NULL stands for “unknown” or “not applicable.” It is not the same as zero or an empty string — it is the absence of a value. When you design a table, you decide whether the foreign key column is allowed to hold NULL or not.

If the foreign key allows NULL, then a record in the child table can exist without being linked to any parent record. In our college example, a student can be enrolled without a department assignment — the department code is left blank.
If the foreign key does not allow NULL (it is “NOT NULL”), then every record in the child table must point to a valid, existing parent record. Every student must belong to a department, no exceptions.

Why does this matter? Because it directly controls the referential integrity of your data — the rule that every foreign key value must match a primary key value in the parent table. A NULL is not a violation of that rule, because NULL simply means “no reference.” But a non-null value that does not match any parent key is a violation.

Important

A NULL foreign key does not break referential integrity. The rule only applies to non-null values. This is a common point of confusion in exams: a foreign key can be NULL unless you explicitly forbid it.

Here are the practical consequences:

When NULL is allowed: You can insert a child record before you know its parent. For example, a new employee can be added to the payroll before their manager is assigned. This gives flexibility, but it also means some records may be “orphaned” in the sense that they have no parent link.
When NULL is forbidden: You must have the parent record exist first. This enforces a stricter relationship — every order must belong to a customer, every transaction must have an account. It prevents incomplete data from entering the system.

In most real-world databases, the decision depends on the business rule. If a relationship is mandatory (every invoice must have a customer), the foreign key is NOT NULL. If it is optional (a product may or may not have a discount code), the foreign key allows NULL.

Note

A NULL foreign key does not mean the parent record was deleted. It simply means no parent was ever assigned. If you delete a parent record that has child records pointing to it, that is a different problem — cascading actions handle that, not nullability.

So, foreign key nullability is a design choice: it decides whether a relationship is mandatory or optional. It is not about right or wrong — it is about accurately modelling the real-world rule your database must follow.

Why Foreign Keys Are Allowed to Have NULL Values

A foreign key constraint ensures referential integrity — every non-NULL value in the foreign key column must match a primary key value in the parent table. But the constraint does not require that every row in the child table be linked to a parent row. That choice is by design.

The reason is simple: not every relationship is mandatory. In the real world, a record may exist without being associated with another record yet, or it may never need that association. NULL in a foreign key means "this child row is currently not linked to any parent row" — it is a valid semantic state, not an error.

Important

A foreign key constraint only enforces that non-NULL values are valid references. NULL values are always allowed unless the column is explicitly declared NOT NULL.

Example

Consider a Student table and a LibraryCard table. Not every student may have a library card on the day of admission.

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE LibraryCard (
    card_id INT PRIMARY KEY,
    student_id INT,
    issue_date DATE,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

Now insert a student who hasn't yet been issued a card:

INSERT INTO Student VALUES (101, 'Ananya');
INSERT INTO LibraryCard VALUES (1, NULL, NULL);

The second insert succeeds because NULL in the foreign key column student_id is permitted — it simply means "this card is not assigned to any student yet." If we later issue the card to Ananya, we update:

UPDATE LibraryCard SET student_id = 101 WHERE card_id = 1;

Now the foreign key constraint kicks in: it checks that 101 exists in Student, which it does.

Watch out

A common mistake is to think a foreign key forces every child row to have a parent. It does not — only that if a parent reference is given, it must be valid. NULL is the "no parent" signal.

✓Final answer

Foreign keys allow NULL values because not all relationships are mandatory — a NULL foreign key simply means the child row is not currently associated with any parent row, which is a valid and useful real-world scenario.

Foreign keys can be NULL because a child row may legitimately exist without being linked to any parent row — NULL means "no relationship," not "invalid relationship."

The question gets at a design choice that often confuses students. A foreign key constraint says: if this column has a value, that value must exist in the parent table's primary key. But the constraint does not require the column to have a value at all. That distinction is the whole point.

Think about real-world data. An employee table might have a DeptID foreign key referencing a department table. What happens when a new employee joins but hasn't been assigned to a department yet? You cannot put a fake department ID — that would violate referential integrity. You cannot leave the employee out of the database — you need their payroll details. The clean solution is to let DeptID be NULL, meaning "not yet assigned." The foreign key constraint still protects you: if someone later types DeptID = 999, the database will reject it because 999 isn't in the department table. But NULL passes through untouched — the constraint simply does not check it.

Note

This is the optional participation rule in database design. A child entity may participate in a relationship optionally (zero occurrences) or mandatorily (at least one). NULL foreign keys implement the optional case.

Here is a concrete example using two tables:

Departments

DeptID	DeptName
10	Sales
20	Engineering
30	HR

Employees (with a foreign key on DeptID referencing Departments(DeptID))

EmpID	EmpName	DeptID
101	Alice	10
102	Bob	20
103	Carol	NULL
104	David	30
105	Eve	NULL

Carol and Eve have NULL in DeptID. This is perfectly valid — Carol might be a new hire awaiting placement, and Eve might be an independent consultant not tied to any department. The database will accept these rows because the foreign key constraint only enforces that non-NULL values exist in the parent table.

Now try inserting INSERT INTO Employees VALUES (106, 'Frank', 99). The database rejects it — 99 is not in Departments.DeptID. The constraint works exactly as designed.

Watch out

A common mistake is thinking a foreign key must always match a parent row. That is only true for non-NULL values. NULL is the exception — it means "no parent," not "invalid parent."

Tip

When designing a schema, ask: "Can this child row exist without a parent?" If yes, make the foreign key nullable. If every child must have a parent (e.g., an order must have a customer), add NOT NULL to the foreign key column.

✓Final answer

Foreign keys are allowed to have NULL values because a child row may exist without being linked to any parent row — NULL represents the absence of a relationship, not a broken one.

←Previous questionQ1 — Give the terms for each of the following: (a) Collection of logically …→Next questionQ3 — Differentiate between: (a) Database state and database schema (b) Prim…

Q.Why foreign keys are allowed to have NULL values? Explain with an example.

🎓Unlock everything free for 14 days

Unlock everything free for 14 days