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.

Rajasthan RbseTextbookSubjective· 3mImportance★★★★★

21% · 3/14 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.

A foreign key may be NULL because the relationship it records can be optional or not yet known — referential integrity only demands that a non-NULL foreign key value must exist in the referenced table's primary key. Example: a new employee who hasn't been assigned to any department yet gets DeptID = NULL; the row is still valid. If the business rule makes the relationship compulsory, you add NOT NULL on the foreign key column yourself.

✓Final answer

Foreign keys are allowed NULL values so that a tuple can exist before/without the related tuple being known (an optional relationship). Referential integrity says: FK value \in referenced primary-key values or NULL. E.g. in EMPLOYEE(EmpID, EName, DeptID) with DeptID referencing DEPARTMENT(DeptID), an unassigned employee is stored as (105, 'Rahul', NULL) — perfectly legal.

A foreign key links a tuple to a tuple in another relation — but sometimes that relationship is optional or not yet decided. So the referential-integrity rule is deliberately written as: every non-NULL foreign key value must match a primary-key value in the referenced relation. NULL means "no related tuple (yet)", and that is legal unless you explicitly add NOT NULL.

The reasoning

The foreign key constraint (referential integrity) exists to prevent dangling references — you must never point at a tuple that does not exist. But there is a second, different situation: not pointing at anything at all. That is not a dangling reference; it is an honest statement that the relationship is unknown or not applicable for this tuple. Relational databases represent exactly this with NULL. Common real cases:

A new employee who has not yet been allotted a department.
A student whose sports preference has not been given yet.
An order whose delivery agent is not yet assigned.

If foreign keys were forced to be non-NULL, you could not store such tuples at all until the related fact existed — losing real data to satisfy a formality.

Worked example in SQL

CREATE TABLE DEPARTMENT (
  DeptID   INT PRIMARY KEY,
  DeptName VARCHAR(30) NOT NULL
);

CREATE TABLE EMPLOYEE (
  EmpID  INT PRIMARY KEY,
  EName  VARCHAR(30) NOT NULL,
  DeptID INT,                                   -- no NOT NULL: relationship is optional
  FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID)
);

INSERT INTO DEPARTMENT VALUES (10, 'Sales'), (20, 'Accounts');

INSERT INTO EMPLOYEE VALUES (101, 'Meera', 10);    -- OK: 10 exists in DEPARTMENT
INSERT INTO EMPLOYEE VALUES (105, 'Rahul', NULL);  -- OK: NULL allowed, dept not yet assigned
INSERT INTO EMPLOYEE VALUES (108, 'Zoya',  30);    -- REJECTED: 30 not in DEPARTMENT

State of EMPLOYEE after these statements:

EmpID	EName	DeptID
101	Meera	10
105	Rahul	NULL

The third INSERT fails with a foreign-key violation (there is no department 30), while Rahul's NULL is accepted.

Key lines explained

FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID) — enforces referential integrity: any non-NULL DeptID in EMPLOYEE must already exist as a primary-key value in DEPARTMENT.
DeptID INT without NOT NULL — this is what makes the relationship optional. Later, UPDATE EMPLOYEE SET DeptID = 20 WHERE EmpID = 105; records the assignment when it happens.
If the rule were "every employee MUST belong to a department", you would write DeptID INT NOT NULL — the designer chooses; the relational model itself does not force it.

✓Final answer

Foreign keys may hold NULL because the relationship they represent can be optional or currently unknown . Referential integrity requires only that every non-NULL foreign-key value matches an existing primary-key value in the referenced relation; NULL means "no related tuple yet" and causes no dangling reference. Example: employee (105, 'Rahul', NULL) in EMPLOYEE(EmpID, EName, DeptID \to DEPARTMENT) is valid — Rahul simply has no department assigned yet.

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

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