NOT NULL and DEFAULT

Make a column required, and give it a value to fall back on when an INSERT leaves it out.

Syntaxcol_name data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)}]

A column is nullable unless you say otherwise: write neither NULL nor NOT NULL and MySQL treats it as though you wrote NULL. NOT NULL makes the column required — the server rejects any statement that would leave a NULL there. DEFAULT is a separate idea: it supplies a value when the column is absent from an INSERT.

When a default actually applies

Only in two cases: the column is not named in the INSERT column list, or you write the keyword DEFAULT as the value. Passing an explicit NULL does not trigger the default.

Expression defaults

A literal default needs no parentheses; an expression default must be wrapped in them — DEFAULT (UUID_TO_BIN(UUID())), DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR). Expression defaults have been permitted since MySQL 8.0.13. BLOB, TEXT, GEOMETRY and JSON columns accept a default only in expression form, so DEFAULT ('abc') works where DEFAULT 'abc' is an error. TIMESTAMP and DATETIME are the exception that needs no parentheses around CURRENT_TIMESTAMP.

Shorthand and longhand

In a DEFAULT clause, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), NOW(), LOCALTIME and LOCALTIMESTAMP are the same thing.

What trips people up

With strict SQL mode on — the 8.4 default — omitting a NOT NULL column with no default is an error and the statement is rolled back. With strict mode off, MySQL silently substitutes the type's implicit default: 0, '', the first ENUM member.

Example

Example · sql
CREATE TABLE subscriptions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_id  BIGINT UNSIGNED NOT NULL,              -- required, no fallback
  plan        VARCHAR(30)     NOT NULL DEFAULT 'free',
  seats       SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  trial_ends  DATE            NULL,                  -- NULL means "no trial"
  metadata    JSON            NOT NULL DEFAULT (JSON_OBJECT()), -- parens required
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                       ON UPDATE CURRENT_TIMESTAMP
);

-- plan, seats, metadata and both timestamps all fill themselves in.
INSERT INTO subscriptions (account_id) VALUES (91);

-- Same effect, written explicitly:
INSERT INTO subscriptions (account_id, plan, seats)
VALUES (92, DEFAULT, DEFAULT);

When to use it

  • Guarantee that every row has a tenant id so a WHERE clause can never silently return another customer's data.
  • Stamp created_at and updated_at without touching application code.
  • Distinguish 'not supplied yet' from 'deliberately zero' by leaving one column nullable and the other NOT NULL DEFAULT 0.

More examples

Strict mode turns a missing value into an error

Shows the two halves of the design working together. NOT NULL without a default makes the column the caller's responsibility; NOT NULL with a default makes it optional but never null. Check @@sql_mode before assuming this error will appear — on a legacy server with strict mode disabled, the first INSERT quietly stores an empty string.

Example · sql
CREATE TABLE invoices (
  id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  number VARCHAR(20) NOT NULL,          -- required, no default
  total  DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

-- Strict mode is on by default in MySQL 8.4:
INSERT INTO invoices (total) VALUES (19.99);
-- ERROR 1364 (HY000): Field 'number' doesn't have a default value

-- Fine: number supplied, total falls back to 0.00
INSERT INTO invoices (number) VALUES ('INV-0001');

Adding a required column to a table that already has rows

The two-step form is what you want when the correct value differs per row and has to be derived rather than assumed. The single-statement form is for the case where one value genuinely fits every existing row.

Example · sql
-- Existing rows need a value, so a bare NOT NULL add would fail
-- (or silently backfill the implicit default outside strict mode).

-- Step 1: add it nullable and backfill explicitly.
ALTER TABLE users ADD COLUMN locale VARCHAR(10) NULL;
UPDATE users SET locale = 'en-GB' WHERE locale IS NULL;

-- Step 2: tighten the definition once every row satisfies it.
ALTER TABLE users MODIFY locale VARCHAR(10) NOT NULL DEFAULT 'en-GB';

-- Or, in one statement, when the default is acceptable for every old row:
ALTER TABLE users ADD COLUMN locale VARCHAR(10) NOT NULL DEFAULT 'en-GB';

Expression defaults, including the parentheses rule

Use expression defaults when the correct value is derivable at insert time and you would rather not trust every client to compute it identically. Note the asymmetry: JSON and TEXT columns accept a default only as an expression, while TIMESTAMP and DATETIME accept CURRENT_TIMESTAMP bare.

Example · sql
CREATE TABLE api_tokens (
  -- Expression default: parentheses are mandatory
  id          BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())) PRIMARY KEY,
  label       VARCHAR(60) NOT NULL DEFAULT '',        -- literal: no parentheses
  scopes      JSON        NOT NULL DEFAULT (JSON_ARRAY('read')),
  expires_on  DATE        NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 90 DAY),
  created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP -- documented exception
);

INSERT INTO api_tokens (label) VALUES ('ci-runner');
-- id, scopes, expires_on and created_at are all computed by the server

Related

sql nullsql coalescesql create tablesql insertsql check

Discussion

  • Be the first to comment on this lesson.