# SQL DEFAULT Constraint: Set a Default Column Value

- URL: https://betteranalyst.com/sql/default

SQL guide · Constraints

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

- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL CHECK](https://betteranalyst.com/sql/check)
- [SQL DROP DEFAULT](https://betteranalyst.com/sql/drop-default)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)
- [SQL INSERT INTO](https://betteranalyst.com/sql/insert-into)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)