NOT NULL

A NOT NULL constraint requires a column to always contain a value — any attempt to insert or update a row with NULL in that column is rejected by the database. It is the simplest constraint in SQL, and one of the most important, since it prevents entire classes of bugs caused by missing data slipping silently into a table.

Declaring NOT NULL

SQL

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  hire_date   DATE NOT NULL,
  middle_name VARCHAR(50)          -- nullable: not every employee has one
);

SQL

-- Fails: last_name is required
INSERT INTO employees (employee_id, first_name, hire_date)
VALUES (1, 'Amara', '2024-01-15');

ERROR: null value in column "last_name" violates not-null constraint
DETAIL: Failing row contains (1, Amara, null, 2024-01-15, null).

Why enforce this at the database level

It would be possible to check "is last_name filled in?" only in application code — a signup form, an admin panel. But any bug, forgotten check, direct database script, or second application accessing the same table can bypass that check. A NOT NULL constraint makes it impossible for a missing value to reach the table at all, no matter what wrote it.

Missing values also complicate almost everything downstream: aggregate functions skip NULLs, comparisons against NULL never evaluate to true, and joins can silently drop or duplicate rows when a NULL is involved unexpectedly. Requiring a value up front avoids all of that ambiguity for columns that should always be known.

Adding NOT NULL to an existing column

SQL

ALTER TABLE employees
  ALTER COLUMN middle_name SET NOT NULL;

Note

Adding a NOT NULL constraint to a column that already has data will fail immediately if any existing row currently has a NULL in that column — the ALTER TABLE statement is rejected with a constraint violation, exactly as if you had tried to insert a NULL directly. Before adding NOT NULL to an existing column, first find and clean up (or backfill with a sensible default) every row where the column is currently NULL:

SQL

-- 1. Find the offending rows
SELECT employee_id FROM employees WHERE middle_name IS NULL;

-- 2. Backfill a value (or delete/otherwise resolve the rows)
UPDATE employees SET middle_name = '' WHERE middle_name IS NULL;

-- 3. Only now is it safe to add the constraint
ALTER TABLE employees
  ALTER COLUMN middle_name SET NOT NULL;

Tip

Default NOT NULL to on for any column that represents a fact that must always exist — names, dates, foreign keys, status codes. Reserve nullable columns for data that is genuinely optional, like a middle name, a secondary phone number, or a field that only applies to some rows.