SQL DEFAULT

What is the SQL DEFAULT keyword?

Assigns an automatic value to a column when no explicit value is supplied.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DEFAULT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift

SQL DEFAULT Full Explanation

DEFAULT sets a predefined value for a column any time an INSERT or UPDATE statement omits that column or explicitly uses the DEFAULT keyword. You declare the default in the table definition or as part of an ALTER TABLE statement. If a row is inserted without specifying the column, the database engine substitutes the default, ensuring non-null data, enforcing business rules, and simplifying application logic. Defaults can be constants, expressions, or database functions such as CURRENT_TIMESTAMP. When multiple columns have defaults, each behaves independently. A column can have only one default at a time; redefining it replaces the previous setting. The default is applied before constraints like CHECK or UNIQUE are validated. If a NOT NULL column has no default and receives no value, the statement fails. Beware that DEFAULT does not retroactively fill existing rows after alteration - only new or updated rows use it.

SQL DEFAULT Syntax

-- During table creation
CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);

-- Add or change later
ALTER TABLE table_name
    ALTER COLUMN column_name SET DEFAULT default_value;

-- Remove default
ALTER TABLE table_name
    ALTER COLUMN column_name DROP DEFAULT;

-- Use DEFAULT in INSERT
INSERT INTO table_name (col1, col2)
VALUES (DEFAULT, 'literal');

SQL DEFAULT Parameters

Example Queries Using SQL DEFAULT

-- 1. Constant default
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO products (id) VALUES (1);
-- 'status' becomes 'active'

-- 2. Expression / function default
CREATE TABLE sessions (
    session_id UUID DEFAULT gen_random_uuid(),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO sessions DEFAULT VALUES;
-- Both columns receive their defaults

-- 3. Using DEFAULT keyword explicitly
INSERT INTO products (id, status) VALUES (2, DEFAULT);
-- Still 'active'

Expected Output Using SQL DEFAULT

Each INSERT populates omitted columns with their declared default value
No errors occur even when NOT NULL constraints exist, provided a default is defined

Use Cases with SQL DEFAULT

Guarantee a minimum viable value when applications omit columns
Auto-populate timestamps, UUIDs, or status flags
Simplify bulk data loads by omitting constant columns
Maintain NOT NULL integrity without extra application code

Common Mistakes with SQL DEFAULT

Expecting DEFAULT to backfill existing rows after an ALTER TABLE; it only affects future operations
Forgetting that NULL can still be inserted explicitly unless the column is NOT NULL
Assuming each database supports function defaults identically; some limit expressions
Overriding a default unintentionally by listing the column with NULL in INSERT

First Introduced In

SQL-92

Frequently Asked Questions

What happens if I insert NULL into a column with a DEFAULT?

If the INSERT specifies NULL explicitly, most databases accept the NULL value even when a default exists. Add a NOT NULL constraint to prevent this.

Will changing a default rewrite my whole table?

No. Altering a default only updates the table metadata. Existing rows are untouched, and no table rewrite occurs.

Can a column have more than one default?

A column can have only one active default. Setting a new one replaces the previous definition.

Is DEFAULT mandatory for NOT NULL columns?

Not technically, but highly recommended. Without a default, every INSERT must supply a value or the operation will fail due to the NOT NULL constraint.