9.5 Mandatory vs Optional Relationships

Level: 9 | Version: v1.1 | Author: Meptrasoft

Overview

A relationship can be mandatory or optional depending on whether the related record is required.

Example

Mandatory:

customer_id INT NOT NULL REFERENCES customers(customer_id)

Every order must have a customer.

Optional:

manager_id INT REFERENCES employees(emp_id)

manager_id can be NULL, so an employee may have no manager.

NOT NULL → Mandatory
NULL allowed → Optional

Quick Points

MANDATORY → NOT NULL → REQUIRED
OPTIONAL  → NULL     → NOT REQUIRED

• Mandatory means the relationship must exist.
• Optional means the relationship may not exist.
• A nullable foreign key commonly represents an optional relationship.
• A NOT NULL foreign key commonly represents a mandatory relationship.

Interview Questions

What is a mandatory relationship?

A relationship where the related record is required.

What is an optional relationship?

A relationship where the related record is not required.

How is a mandatory relationship commonly implemented?

Using a foreign key with NOT NULL.

How is an optional relationship commonly implemented?

Using a foreign key that allows NULL.

Practice

1. Identify whether customer_id NOT NULL is mandatory or optional.
2. Identify whether a nullable manager_id is mandatory or optional.
3. Give one real-world example of each.

💡 Tip: Test your queries and schema definitions using the in-browser interactive runner above.

Key Takeaway

NOT NULL → MANDATORY
NULL     → OPTIONAL

Mandatory relationships require a related record; optional relationships allow the relationship to be absent.

End of Mandatory vs Optional Relationships