When to Use NOT NULL vs NULL in Database Design: Practical Guidelines

V哥 explains that the decision to apply NOT NULL or allow NULL is a crucial part of database design, directly affecting data integrity, query performance, and business‑logic complexity.

1. Fields that must always have a value

Critical columns should be defined with NOT NULL to prevent incomplete data and related business issues.

Example: User registration

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Data integrity: guarantees every user has a username and email.

Performance: NOT NULL columns can improve index efficiency.

2. Optional fields

When a column is optional and its absence does not break business logic, allowing NULL provides flexibility and avoids forcing default values.

Example: User profile

CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    middle_name VARCHAR(255),
    profile_picture VARCHAR(255)
);

Flexibility: users can omit optional information.

Business adaptability: NULL accommodates edge cases without artificial defaults.

3. Fields representing an unknown state

In some scenarios NULL explicitly denotes “unknown” or “not provided”, which is clearer than using placeholder values.

Example: Order processing

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    shipped_date DATE
);

State expression: NULL shows the order has not been shipped.

Simplified logic: no extra boolean column is needed.

4. Foreign‑key fields

Use NOT NULL when the relationship is mandatory; allow NULL when the association is optional.

Example: Blog post and author

CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id INT REFERENCES users(id) -- can be NULL
);

Enforced relationship: NOT NULL ensures every post has an author.

Flexible association: NULL permits posts without a specific author.

5. Performance and storage overhead

NOT NULL

can speed up queries for high‑frequency columns because the engine does not need to handle NULL checks, but overusing it may increase schema rigidity.

Example: High‑frequency query field

CREATE TABLE user_sessions (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    last_login TIMESTAMP NOT NULL
);

6. Versioning and evolution

Allowing NULL leaves room for future extensions that were not anticipated during initial design.

Example: Product upgrade

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_rate DECIMAL(5,2) -- can be NULL, meaning no discount
);

7. Data‑driven design

Using NULL to represent missing data yields more accurate analytics than inserting dummy default values.

Example: User activity tracking

CREATE TABLE user_activity (
    user_id INT PRIMARY KEY,
    last_login DATE NOT NULL,
    last_purchase_date DATE -- can be NULL, meaning no purchase yet
);

Accuracy: NULL clearly indicates “no purchase”.

Analysis efficiency: IS NULL filters users without the event.

8. Migration and data compatibility

During schema migration or cross‑system integration, permitting NULL often improves compatibility and reduces data‑loss risk.

Example: Cross‑system address migration

CREATE TABLE user_addresses (
    user_id INT PRIMARY KEY,
    address_line_1 VARCHAR(255) NOT NULL,
    address_line_2 VARCHAR(255) -- allows NULL because it is optional
);

Higher migration success: NULL accommodates mismatched source schemas.

Accurate mapping: avoids inserting meaningless placeholder data.

9. Combining NOT NULL with default values

Pairing NOT NULL with a sensible default makes a column robust while preventing missing data during inserts.

Example: Order status management

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    order_status VARCHAR(50) NOT NULL DEFAULT 'Pending'
);

Business robustness: ensures every order has an initial status.

Ease of maintenance: default reduces insertion complexity.

10. Dynamic structures and JSON type

JSON columns are typically nullable, allowing storage of irregular or evolving data without altering the schema.

Example: User preferences

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    preferences JSON -- allows NULL, meaning no preferences set
);

Flexibility: NULL + JSON handles dynamic, non‑uniform data.

Scalability: future changes can be stored in the JSON field without schema modifications.

Summary

Choosing between NOT NULL and NULL should be driven by business requirements, data accuracy, performance considerations, and future extensibility. Critical fields that enforce data consistency and are frequently queried merit NOT NULL, while optional, unknown‑state, or evolving columns benefit from allowing NULL.