Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL NOT NULL constraint

Reference for the SQL NOT NULL constraint: require a value in a column, combine it with DEFAULT, add it to an existing table and see dialect differences.

A NOT NULL constraint forbids NULL in a column. Every insert must supply a value, either explicitly or through a DEFAULT, and no update may set the column back to NULL. Without the constraint a column accepts NULL by default.

NULL means "unknown or missing", and it infects comparisons and arithmetic, so each nullable column adds cases to every query that touches it. Declaring a column NOT NULL whenever a value is always expected removes those cases and gives the optimizer more to work with.

SQL
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  note TEXT
);

Try it

SQL NOT NULL rejects a missing value (fails on purpose at the end)

Press Run (or Ctrl + Enter) to see the result here.

SQL NOT NULL together with DEFAULT (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
column_name data_type NOT NULL

-- add to an existing column (PostgreSQL, Oracle's MODIFY, SQL Server and MySQL differ)
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
Part Description
NOT NULL The column must always hold a value. It has no arguments.
NULL The opposite: explicitly allows NULL (the default in most systems, written for readability).

Notes: the constraint is checked on every INSERT and UPDATE. A column that is NOT NULL without a DEFAULT must be listed in every INSERT.

Dialect differences

Dialect Notes
SQLite ALTER TABLE ... ADD COLUMN ... NOT NULL requires a non-null DEFAULT. You cannot change an existing column to NOT NULL; rebuild the table.
PostgreSQL ALTER TABLE t ALTER COLUMN c SET NOT NULL (and DROP NOT NULL). It scans the table to check.
MySQL ALTER TABLE t MODIFY c INT NOT NULL, which repeats the full column definition.
SQL Server ALTER TABLE t ALTER COLUMN c INT NOT NULL, which also repeats the type.
Oracle ALTER TABLE t MODIFY (c NOT NULL). Oracle treats the empty string '' as NULL, so it violates NOT NULL.
SQL
-- PostgreSQL
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

-- MySQL
ALTER TABLE products MODIFY name VARCHAR(100) NOT NULL;

-- SQL Server
ALTER TABLE products ALTER COLUMN name NVARCHAR(100) NOT NULL;

Usage notes

  • Default to NOT NULL. Allow NULL only when "no value" is a real state, such as a missing end date.
  • An empty string is not NULL (except in Oracle). NOT NULL still lets '' through, so add a CHECK such as CHECK (name <> '') if blanks are not acceptable.
  • A default is not a guard. DEFAULT 0 fills the value only when the column is left out. Explicitly inserting NULL still fails with NOT NULL.
  • Adding it to a table with data fails if any row already holds NULL. Backfill the rows first with an UPDATE, then add the constraint.
  • Primary keys imply NOT NULL in the standard (SQLite excepts some cases).
  • Aggregates skip NULL. A NOT NULL column makes COUNT(col) equal COUNT(*), which removes a common source of confusion.
  • Name the error. The message names the table and column: "NOT NULL constraint failed: members.name" in SQLite.