NOT NULL Constraint
SQL · Working with Tables
Theory
NOT NULL says a column must always have a value. Any INSERT that leaves the column out (with no DEFAULT) or sets it to NULL, and any UPDATE that sets it to NULL, is rejected with an error. By default columns are nullable, so you add NOT NULL to required fields like name, order_date or amount.
NOT NULL pairs well with DEFAULT: created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP is filled automatically when omitted. It does not stop empty strings or zero, which are real values; use CHECK for that. Adding NOT NULL to an existing column fails if the column already holds NULLs, so you first fill them with UPDATE. Primary key columns are implicitly NOT NULL in standard SQL.
Why we use it
Missing required values break reports, joins and application logic. NOT NULL stops them at the door, so every query can trust that the column is filled and does not need extra NULL handling.
When to use it
Put NOT NULL on any column the business treats as required. Leave a column nullable only when "unknown" or "not applicable" is a real state, like an end_date for a current employee.
How it works
- INSERT or UPDATE arrives
- Find the value for each NOT NULL column
- Was the column left out? Yes: Use its DEFAULT if one exists. No: Use the given value.
- Is the final value NULL? Yes: Reject with an error. No: Accept the row.
- Row stored with all required values
Syntax
column datatype NOT NULLExample
CREATE TABLE employees (
name VARCHAR(100) NOT NULL
);Key points
- Enforced at insert and update time
- Common on required business fields
- Empty string '' and 0 are values, so they pass
- Combine with DEFAULT to auto-fill omitted values
- Adding NOT NULL later fails if existing rows hold NULLs