Eric Campbell 4 min read SQL

SQL - NOT NULL Constraint

The NOT NULL constraint ensures a column cannot contain NULL values. Unlike other constraints that validate relationships or value ranges, NOT NULL addresses the fundamental question: must this field...

Key Insights

  • NOT NULL constraints enforce data integrity at the database level, preventing null values in critical columns and eliminating an entire class of application bugs before they occur
  • While simple to implement, NOT NULL constraints require careful planning during schema design and strategic approaches when adding them to existing tables with data
  • Combining NOT NULL with other constraints like CHECK and DEFAULT creates robust data validation layers that reduce defensive coding in application logic

Understanding NOT NULL Constraints

The NOT NULL constraint ensures a column cannot contain NULL values. Unlike other constraints that validate relationships or value ranges, NOT NULL addresses the fundamental question: must this field always have a value?

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    bio TEXT,
    created_at TIMESTAMP NOT NULL
);

In this schema, email, username, and created_at must always contain values. The bio field remains optional, reflecting real-world requirements where users might not provide biographical information.

Attempting to insert incomplete data fails immediately:

-- This fails
INSERT INTO users (user_id, email, created_at) 
VALUES (1, 'user@example.com', NOW());

-- Error: Column 'username' cannot be null

Adding NOT NULL to Existing Tables

Adding NOT NULL constraints to populated tables requires a multi-step approach. You cannot simply add the constraint if existing rows contain NULL values.

-- Step 1: Identify NULL values
SELECT COUNT(*) 
FROM products 
WHERE product_name IS NULL;

-- Step 2: Update NULL values
UPDATE products 
SET product_name = 'Unnamed Product' 
WHERE product_name IS NULL;

-- Step 3: Add the constraint
ALTER TABLE products 
MODIFY COLUMN product_name VARCHAR(255) NOT NULL;

For PostgreSQL, the syntax differs slightly:

ALTER TABLE products 
ALTER COLUMN product_name SET NOT NULL;

In production environments with large tables, this operation can lock the table. PostgreSQL 12+ offers a non-blocking approach:

-- Add a CHECK constraint first (doesn't lock for long)
ALTER TABLE products 
ADD CONSTRAINT product_name_not_null 
CHECK (product_name IS NOT NULL) NOT VALID;

-- Validate it (scans table but allows reads/writes)
ALTER TABLE products 
VALIDATE CONSTRAINT product_name_not_null;

-- Convert to NOT NULL constraint
ALTER TABLE products 
ALTER COLUMN product_name SET NOT NULL;

-- Drop the CHECK constraint
ALTER TABLE products 
DROP CONSTRAINT product_name_not_null;

NOT NULL with DEFAULT Values

Combining NOT NULL with DEFAULT values ensures columns always have meaningful data without requiring explicit values in INSERT statements:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

This allows simplified inserts:

-- Only specify required business data
INSERT INTO orders (order_id, customer_id) 
VALUES (1001, 42);

-- Results in:
-- order_id: 1001
-- customer_id: 42
-- order_status: 'pending'
-- order_date: current timestamp
-- total_amount: 0.00

DEFAULT values work particularly well for audit fields:

CREATE TABLE documents (
    document_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    version INT NOT NULL DEFAULT 1
);

NOT NULL in Foreign Key Relationships

Foreign keys can be nullable or NOT NULL, each serving different purposes. A NOT NULL foreign key enforces a mandatory relationship:

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Every order item must belong to an order and reference a product. Contrast this with optional relationships:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INT,
    department_id INT NOT NULL,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, manager_id is nullable (CEO has no manager), while department_id is NOT NULL (every employee must belong to a department).

Conditional NOT NULL Logic

Sometimes you need context-dependent nullability. While databases don’t support conditional NOT NULL directly, you can use CHECK constraints:

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    payment_method VARCHAR(20) NOT NULL,
    credit_card_number VARCHAR(16),
    bank_account_number VARCHAR(20),
    CONSTRAINT payment_details_required CHECK (
        (payment_method = 'credit_card' AND credit_card_number IS NOT NULL) OR
        (payment_method = 'bank_transfer' AND bank_account_number IS NOT NULL) OR
        (payment_method NOT IN ('credit_card', 'bank_transfer'))
    )
);

This ensures credit card payments include card numbers and bank transfers include account numbers:

-- Valid
INSERT INTO payments (payment_id, payment_method, credit_card_number)
VALUES (1, 'credit_card', '1234567890123456');

-- Invalid - missing credit card number
INSERT INTO payments (payment_id, payment_method)
VALUES (2, 'credit_card');

Performance Considerations

NOT NULL constraints improve query performance. The optimizer knows these columns always contain values, enabling better execution plans:

CREATE INDEX idx_active_users ON users(last_login_date) 
WHERE last_login_date IS NOT NULL;

-- With NOT NULL constraint, simpler index:
CREATE INDEX idx_active_users ON users(last_login_date);

NOT NULL columns also consume less storage in some databases. PostgreSQL uses a bitmap to track NULL values; NOT NULL columns skip this overhead.

Testing NOT NULL Constraints

Verify constraint behavior in your test suite:

-- Test NOT NULL enforcement
DO $$
BEGIN
    INSERT INTO users (user_id, email, created_at)
    VALUES (999, 'test@example.com', NOW());
    RAISE EXCEPTION 'NOT NULL constraint failed to trigger';
EXCEPTION
    WHEN not_null_violation THEN
        RAISE NOTICE 'NOT NULL constraint working correctly';
END $$;

For application testing, ensure your ORM or database library properly handles constraint violations:

import psycopg2

try:
    cursor.execute(
        "INSERT INTO users (user_id, email, created_at) VALUES (%s, %s, %s)",
        (1, 'user@example.com', datetime.now())
    )
except psycopg2.IntegrityError as e:
    if 'not-null constraint' in str(e):
        # Handle missing required field
        return {"error": "Username is required"}

Migration Strategies

When adding NOT NULL constraints in production, use a phased approach:

-- Phase 1: Add column as nullable
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

-- Phase 2: Populate data over time
UPDATE customers 
SET phone = legacy_phone_data 
WHERE phone IS NULL AND legacy_phone_data IS NOT NULL;

-- Phase 3: Set default for remaining NULLs
UPDATE customers 
SET phone = 'Not Provided' 
WHERE phone IS NULL;

-- Phase 4: Add NOT NULL constraint
ALTER TABLE customers 
ALTER COLUMN phone SET NOT NULL;

This approach minimizes downtime and allows data quality issues to be addressed incrementally rather than blocking the entire migration.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.