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.
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, note TEXT );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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. AllowNULLonly when "no value" is a real state, such as a missing end date. - An empty string is not
NULL(except in Oracle).NOT NULLstill lets''through, so add aCHECKsuch asCHECK (name <> '')if blanks are not acceptable. - A default is not a guard.
DEFAULT 0fills the value only when the column is left out. Explicitly insertingNULLstill fails withNOT NULL. - Adding it to a table with data fails if any row already holds
NULL. Backfill the rows first with anUPDATE, then add the constraint. - Primary keys imply
NOT NULLin the standard (SQLite excepts some cases). - Aggregates skip
NULL. ANOT NULLcolumn makesCOUNT(col)equalCOUNT(*), 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.