SQL NOT NULL Constraint

The NOT NULL constraint is a rule you attach to a column that forbids storing the absence of a value in that column. Once applied, every row inserted or updated in the table must supply a real value for that column — the database engine will refuse the operation otherwise. It is one of the simplest constraints in SQL, but it is also one of the most important for data quality, because a column that silently accepts NULL tends to accumulate incomplete rows that break reports, joins, and application logic down the line.

Overview / How It Works

In SQL, NULL is a special marker meaning "unknown" or "no value," and it is distinct from an empty string, zero, or false. By default, every column in a table allows NULL unless you tell the engine otherwise. The NOT NULL constraint changes that default: it tells the storage engine that any attempt to write a row where this column’s value is missing must be rejected before the row is committed.

Under the hood, the constraint is checked at the column level as part of row validation, which happens after any DEFAULT value has been substituted for an omitted column but before the row is physically written to disk. If you omit a NOT NULL column entirely from an INSERT and it has no DEFAULT, the engine tries to use NULL as the value, immediately fails the NOT NULL check, and aborts the statement with an error such as NOT NULL constraint failed: table.column. The same check runs again on every UPDATE that touches that column.

NOT NULL is a column-level constraint, unlike PRIMARY KEY or FOREIGN KEY, which can span multiple columns. A table can have any number of NOT NULL columns, and it is common (and good practice) to combine NOT NULL with other constraints such as DEFAULT, CHECK, or UNIQUE on the same column. In standard SQL, a PRIMARY KEY column is implicitly NOT NULL — you never need to write it explicitly on a primary key. SQLite mostly follows this rule but has one well-known quirk: an INTEGER PRIMARY KEY column is an alias for the table’s internal row id, and if you insert NULL into it, SQLite silently substitutes the next available integer rather than raising a NOT NULL error. Other engines like MySQL, PostgreSQL, and SQL Server enforce NOT NULL on primary keys literally, with no such substitution.

Syntax

The constraint is written directly after a column’s data type, either when creating a table or when adding a new column to an existing one.

CREATE TABLE table_name (
    column_name data_type NOT NULL,
    other_column data_type NOT NULL DEFAULT default_value
);
Part Description
column_name data_type The column being constrained and its type; NOT NULL is written immediately after the type.
NOT NULL Forbids NULL for this column on both INSERT and UPDATE.
DEFAULT default_value Optional. Supplies a value automatically when the column is omitted, satisfying NOT NULL without the caller specifying anything.
CHECK (expression) Optional companion constraint; NOT NULL only blocks missing values, not "empty-looking" ones like '', so a CHECK is often added alongside it.

Here is a minimal, runnable illustration of the syntax:

CREATE TABLE syntax_demo (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO syntax_demo (id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com');

SELECT * FROM syntax_demo;

Result:

id username email
1 jdoe jdoe@example.com

Examples

Example 1: Mixing NOT NULL and nullable columns

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT
);

INSERT INTO students (student_id, first_name, last_name, email)
VALUES
    (1, 'Ava', 'Nguyen', 'ava.nguyen@example.com'),
    (2, 'Marco', 'Silva', NULL);

SELECT * FROM students;

Result:

student_id first_name last_name email
1 Ava Nguyen ava.nguyen@example.com
2 Marco Silva NULL

Both rows succeed. first_name and last_name are NOT NULL, and every row supplies real values for them, so the constraint never fires. email has no such constraint, so Marco’s row is allowed to leave it as NULL. This shows that NOT NULL is applied per column, not per table — you decide exactly which fields are mandatory.

Example 2: Adding a NOT NULL column to a table that already has rows

CREATE TABLE products_catalog (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL
);

INSERT INTO products_catalog (product_id, product_name) VALUES (1, 'Wireless Mouse');
INSERT INTO products_catalog (product_id, product_name) VALUES (2, 'USB-C Cable');

ALTER TABLE products_catalog ADD COLUMN category TEXT NOT NULL DEFAULT 'Uncategorized';

INSERT INTO products_catalog (product_id, product_name, category) VALUES (3, 'Mechanical Keyboard', 'Electronics');

SELECT * FROM products_catalog;

Result:

product_id product_name category
1 Wireless Mouse Uncategorized
2 USB-C Cable Uncategorized
3 Mechanical Keyboard Electronics

This is the pattern you need whenever you add a mandatory column to a table that already contains data. If you tried to add category TEXT NOT NULL without a DEFAULT, the engine would have no value to put in the existing rows and would reject the ALTER TABLE outright. Supplying DEFAULT 'Uncategorized' lets the engine backfill the two existing rows automatically, while the new row for the keyboard supplies its own explicit value.

Example 3: Using NOT NULL semantics to reason about a query

SELECT name, department, salary
FROM employees
WHERE department IS NULL;

Result: no rows.

Against the sample employees table, this query returns nothing, because every row has a real department value. If department were declared NOT NULL, you could rely on that guarantee — a query like this one would always return zero rows, and any code downstream (a report, an API response, a join) would never need to special-case a missing department. This is the practical payoff of the constraint: it turns an assumption you’d otherwise have to defend against everywhere into a fact enforced once, at the schema level.

How It Works Step by Step / Under the Hood

When you run an INSERT or UPDATE against a table with NOT NULL columns, the engine works through these steps for each affected row:

  1. Resolve values. For every column, the engine determines the value to store: the value you supplied, or, if you omitted the column, its DEFAULT expression, or NULL if there is no default.
  2. Apply type affinity. The resolved value is coerced to the column’s declared type where applicable (SQLite uses type affinity rather than strict typing for most columns).
  3. Check NOT NULL. For each column marked NOT NULL, the engine checks whether the resolved value is NULL. If it is, the statement fails immediately with a constraint violation and no row is written.
  4. Check other constraints. CHECK, UNIQUE, and foreign key constraints are evaluated next.
  5. Write the row. Only once every constraint passes does the engine commit the row to the table’s storage.

Because this check happens per statement (and, for multi-row statements, typically per row within that statement), a single bad row in a multi-row INSERT can abort the whole statement, meaning none of the rows in that statement are written. This is different from a CHECK constraint you add later purely for documentation — NOT NULL is always active and always cheap to check, since it requires no expression evaluation beyond "is this value missing."

Common Mistakes

Mistake 1: Adding a NOT NULL column without a DEFAULT to a non-empty table

ALTER TABLE employees ADD COLUMN manager_name TEXT NOT NULL;

This fails. The employees table already has rows, and there is no value the engine can put in manager_name for those existing rows — without a DEFAULT, it would have to be NULL, which directly violates the constraint you’re trying to add. The engine refuses the whole ALTER TABLE rather than leave the table in an inconsistent state.

Corrected: supply a default so existing rows have something to backfill with, exactly as shown in Example 2 above (ALTER TABLE products_catalog ADD COLUMN category TEXT NOT NULL DEFAULT 'Uncategorized'). You can update the backfilled value afterward with a normal UPDATE if 'Uncategorized' isn’t accurate long-term.

Mistake 2: Assuming NOT NULL blocks empty or blank values

CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    phone TEXT NOT NULL
);

INSERT INTO contacts (contact_id, phone) VALUES (1, '');

SELECT * FROM contacts;

Result: one row: contact_id = 1, phone = '' (an empty string, not NULL).

This insert succeeds, which surprises a lot of beginners. NOT NULL only blocks the special NULL marker — it says nothing about empty strings, all-whitespace strings, or zero. If "a phone number was actually provided" is the real requirement, NOT NULL alone doesn’t enforce it.

Corrected: pair NOT NULL with a CHECK constraint that also rejects blank values:

CREATE TABLE contacts_v2 (
    contact_id INTEGER PRIMARY KEY,
    phone TEXT NOT NULL CHECK (length(trim(phone)) > 0)
);

INSERT INTO contacts_v2 (contact_id, phone) VALUES (1, '555-0100');

SELECT * FROM contacts_v2;

Now attempting to insert an empty string is rejected too:

INSERT INTO contacts_v2 (contact_id, phone) VALUES (2, '');

This second insert fails with a CHECK constraint failed error, because length(trim('')) is 0, which does not satisfy > 0. Combining NOT NULL with a targeted CHECK closes the gap that NOT NULL alone leaves open.

Best Practices

  • Default to NOT NULL for any column that your application logic assumes will always have a value — names, foreign keys, timestamps, statuses. Only allow NULL when "unknown" or "not applicable" is a genuinely valid state.
  • When adding a NOT NULL column to a table that already has data, always provide a DEFAULT so the ALTER TABLE can backfill existing rows.
  • Remember that NOT NULL does not forbid empty strings, all-whitespace text, or zero — add a CHECK constraint if those should also be disallowed.
  • Declare NOT NULL at table-creation time whenever possible; it is far cheaper to design it in up front than to retrofit it once bad data already exists in the column.
  • Combine NOT NULL with DEFAULT for optional-but-not-blank fields (like a status column that should default to 'pending') so callers aren’t forced to specify every column on every insert.
  • Before adding NOT NULL to an existing column that may already contain NULLs, first run a query like SELECT COUNT(*) FROM table WHERE column IS NULL and clean up the offending rows before applying the constraint.

Practice Exercises

  • Exercise 1: Create a table called books with columns book_id, title (mandatory), and isbn (optional). Insert two rows: one with both fields filled in, and one that omits isbn. Confirm both inserts succeed.
  • Exercise 2: Starting from the employees sample table, write the ALTER TABLE statement that adds a new NOT NULL column named status with a default value of 'active', so it succeeds even though the table already has four rows. Then query the table to confirm all existing rows show 'active'.
  • Exercise 3: Design a reviews table where a rating column must always be present (NOT NULL) and must also be a whole number between 1 and 5. Write the single CREATE TABLE statement that enforces both rules together.

Summary

  • NOT NULL is a column-level constraint that rejects any INSERT or UPDATE leaving that column without a value.
  • The check runs after DEFAULT substitution and before the row is written, so an omitted column with a default still passes.
  • PRIMARY KEY columns are implicitly NOT NULL in standard SQL; SQLite’s INTEGER PRIMARY KEY has a special rowid-substitution quirk worth knowing about.
  • Adding NOT NULL to an existing column via ALTER TABLE requires a DEFAULT if the table already has rows, so the engine has something to backfill with.
  • NOT NULL does not block empty strings or whitespace — pair it with a CHECK constraint when blank values should also be disallowed.
  • Designing columns as NOT NULL from the start is far easier than retrofitting the constraint after bad data has accumulated.