Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL DEFAULT constraint

Reference for the SQL DEFAULT constraint: fill in a value when none is given, constants and expressions, DEFAULT VALUES, NULL behavior and dialect differences.

A DEFAULT clause gives a column a value to use when an INSERT does not mention that column. It saves repetition in application code and makes sure every row gets a sensible starting value, such as a status of 'pending', a quantity of 0 or the current timestamp.

A default applies only when the column is left out of the insert, or when you ask for it with the keyword DEFAULT where the dialect allows it. Writing NULL explicitly does not trigger it: that stores NULL.

SQL
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'open',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Try it

SQL Defaults fill in omitted columns

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

SQL An explicit NULL is not replaced by the default (fails on purpose at the end)

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

Syntax

SQL
column_name data_type DEFAULT default_value [constraints]

INSERT INTO table_name DEFAULT VALUES;                -- SQLite, PostgreSQL, SQL Server
INSERT INTO table_name (column_name) VALUES (DEFAULT); -- PostgreSQL, MySQL, SQL Server, Oracle
Part Description
default_value A constant (number, string, NULL), a keyword such as CURRENT_TIMESTAMP, or in many systems an expression or function call. SQLite requires non-constant expressions to be wrapped in parentheses, for example DEFAULT (date('now')).
DEFAULT VALUES Inserts one row made only of defaults and generated keys.
DEFAULT keyword in VALUES Asks for the default of one column inside a normal insert list.

Notes: without a DEFAULT clause, the default of a column is NULL. The default must fit the column's type, and a default for a NOT NULL column must not be NULL.

Dialect differences

Dialect Notes
SQLite No DEFAULT keyword inside VALUES. Expression defaults need parentheses. Cannot change a default with ALTER TABLE.
PostgreSQL ALTER TABLE t ALTER COLUMN c SET DEFAULT expr and DROP DEFAULT. Defaults may call functions such as now() or gen_random_uuid().
MySQL DEFAULT (expression) since 8.0.13; ON UPDATE CURRENT_TIMESTAMP keeps a timestamp in step with changes.
SQL Server A default is a named constraint object: CONSTRAINT df_status DEFAULT 'open'. Use GETDATE() for the current time.
Oracle DEFAULT ON NULL (12c) also replaces explicit NULLs. Use SYSDATE or CURRENT_TIMESTAMP.
SQL
-- PostgreSQL
ALTER TABLE tasks ALTER COLUMN status SET DEFAULT 'open';

-- SQL Server
ALTER TABLE tasks ADD CONSTRAINT df_tasks_status DEFAULT 'open' FOR status;

-- Oracle 12c and later
CREATE TABLE tasks (id NUMBER, status VARCHAR2(10) DEFAULT ON NULL 'open');

Usage notes

  • Omitted means default; NULL means NULL. Application code that passes NULL for "no value" silently bypasses the default.
  • Evaluated at insert time. CURRENT_TIMESTAMP is the moment of the insert, not of the table creation.
  • Pair with NOT NULL. A NOT NULL DEFAULT column is both required and convenient.
  • Adding a column with a default to an existing table fills old rows with the default (instantly in modern PostgreSQL, MySQL 8 and SQLite for constant defaults).
  • Changing a default affects future inserts only, not existing rows.
  • Time zones: in SQLite CURRENT_TIMESTAMP is UTC text like 2025-03-01 12:30:00.
  • Defaults are not validation. Add a CHECK to restrict allowed values.