Database Management System (DBMS) / Relational Model

Can Foreign Key have NULL values?

✅ Yes, a foreign key column can have NULL values, unless it is explicitly restricted by a NOT NULL constraint.

📌 Why?

A foreign key means that the value must match a primary key in another table, or it can be left empty (NULL) if:

  • The relationship is optional

  • The data is not yet known

  • The design allows it

🧾 Example:

🧩 Department Table (Parent)

DeptID (PK)

DeptName

10

Computer Science

20

Mathematics

👩‍🎓 Student Table (Child)

StudentID (PK)

Name

DeptID (FK)

101

Alice

10

102

Bob

20

103

Zara

NULL ✅

✅ Zara’s DeptID is NULL — which is valid because:

  • It does not violate referential integrity.

  • It just means Zara is not currently assigned to any department.

🧪 SQL Example:

CREATE TABLE Department (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(50),
  DeptID INT, -- Nullable foreign key
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

🔐 Important Points:

Rule

Explanation

✅ NULL is allowed

A foreign key can be NULL if no NOT NULL constraint is applied

❌ NULL ≠ violation

A NULL in foreign key does not need to match anything in the parent table

🔒 Still maintains integrity

Because NULL means “unknown” or “not applicable,” and not “invalid”

🔁 Real-Life Analogy:

A student admission form may have a field for "Hostel Room Number":

  • If the student doesn't stay in the hostel → that field is left empty (NULL)

  • It’s not wrong, just optional

✅ Summary:

Question

Answer

Can a foreign key be NULL?

✅ Yes

Is it a violation?

❌ No

When is it not allowed?

If NOT NULL is specified in the column definition