SQL guide · Constraints

SQL DEFAULT Constraint: Set a Default Column Value

A DEFAULT constraint tells the database which value to store in a column when an INSERT does not provide one. Instead of getting NULL, the row receives the default, which might be a constant such as 0 or 'pending', or an expression like CURRENT_TIMESTAMP. This keeps inserts short and ensures that important columns always start in a sensible state.

Defaults are most useful for audit and status fields: a created_at timestamp, an is_active flag that starts true, a status that starts as pending, or a quantity that starts at one. They pair naturally with NOT NULL, because together they say that the column must always have a value and that you do not need to provide it yourself. A default is only used when the column is omitted from the insert, or when you explicitly write the keyword DEFAULT.

It is important to understand what a default does not do. It does not apply to updates, it does not replace values that you explicitly set to NULL, and it does not validate anything. If you insert NULL into a nullable column that has a default, the column ends up null. Changing a default also does not alter rows that already exist, it only affects future inserts.

Basic syntax

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;   -- PostgreSQL, MySQL

INSERT INTO table_name (col1, col2)
VALUES (value1, DEFAULT);

default_value can be a literal, or a function or expression the database allows, such as CURRENT_DATE. In SQL Server, a default is a named constraint added with ADD CONSTRAINT ... DEFAULT ... FOR column, shown in the dialect notes.

SQL DEFAULT examples

Defaults for status and flags

New orders start as pending and active customers default to true, so the insert only needs the values that actually vary. Omitted columns pick up their defaults.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status   VARCHAR(20) NOT NULL DEFAULT 'pending',
  priority INT NOT NULL DEFAULT 3
);

INSERT INTO orders (order_id, customer_id)
VALUES (1001, 42);
-- status = 'pending', priority = 3

Automatic timestamps

Using CURRENT_TIMESTAMP records when each row was created without the application sending a time. This is a standard way to build an audit trail and it uses the database server's clock.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Change a default on an existing column

Use SET DEFAULT to change the value used for future inserts. Existing rows keep whatever they already hold. The DEFAULT keyword in a values list also forces the default explicitly.

-- PostgreSQL / MySQL
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'new';

INSERT INTO orders (order_id, customer_id, status)
VALUES (1002, 42, DEFAULT);

SQL Server named default constraint

SQL Server treats a default as its own constraint object attached to a column. Giving it a name makes it easy to drop or replace later, which matters because you cannot drop a column that still has a default attached.

ALTER TABLE orders
ADD CONSTRAINT df_orders_status DEFAULT 'pending' FOR status;

Where SQL dialects differ

PostgreSQL: Any expression is allowed, including functions such as now() and gen_random_uuid(). Change with ALTER COLUMN ... SET DEFAULT and remove with DROP DEFAULT. Defaults cannot reference other columns, although generated columns (version 12 and later) cover that case.

MySQL: Before 8.0.13 only constants and CURRENT_TIMESTAMP were allowed, and BLOB/TEXT columns could not have defaults. From 8.0.13 expression defaults are supported if wrapped in parentheses, for example DEFAULT (UUID()).

SQL Server: Defaults are named constraints: ADD CONSTRAINT df_name DEFAULT value FOR column. If you do not name it, the system makes up a name such as DF__orders__status__3B75D760, which complicates scripts. Use GETDATE() or SYSDATETIME() for timestamps.

SQLite: Defaults in CREATE TABLE accept constants, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, or an expression in parentheses. Changing a default on an existing column is not supported with ALTER TABLE; rebuild the table.

Oracle: Place DEFAULT before NOT NULL, as in status VARCHAR2(20) DEFAULT 'pending' NOT NULL. Use SYSDATE or SYSTIMESTAMP for dates. Change with ALTER TABLE t MODIFY (c DEFAULT value). From 12c, DEFAULT ON NULL also replaces explicit nulls.

Common mistakes

  • Expecting a default to fill in an explicit NULL. Inserting NULL stores null; only omitting the column or writing DEFAULT triggers the default.
  • Thinking a new default updates existing rows. It only applies to rows inserted afterwards, so back-fill old data with an UPDATE.
  • Forgetting that a default is not validation. A column with DEFAULT 'pending' still accepts any other string unless you add a CHECK.
  • Leaving SQL Server defaults unnamed, then being unable to script their removal reliably.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator