DEFAULT Constraint in SQL: Setting Default Column Values

Define sensible default values for columns so inserts stay clean and your schema handles missing input gracefully.

Every new product added to the catalogue should start as active. Every new order should default to a status of 'Pending'. Without a DEFAULT constraint, your application code has to remember to supply these values on every insert, and when it forgets, you end up with NULL in columns that should always have a value. Defining defaults in the schema removes that dependency entirely.

What the DEFAULT Constraint Does

DEFAULT specifies the value the database assigns to a column when an INSERT statement omits that column. The default is used only at insert time. It has no effect on existing rows or on UPDATE statements.

-- products table with DEFAULT values on key columns
CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  TEXT    NOT NULL,
    category_id   INTEGER NOT NULL,
    price         REAL    NOT NULL,
    cost_price    REAL    NOT NULL,
    stock_qty     INTEGER NOT NULL DEFAULT 0,
    is_active     INTEGER NOT NULL DEFAULT 1,
    created_at    TEXT    NOT NULL DEFAULT (DATETIME('now'))
);

stock_qty defaults to zero because a brand new product has no stock yet. is_active defaults to 1 because new products go live by default. created_at defaults to the current timestamp so you never have to pass it manually.

-- This INSERT works without supplying stock_qty, is_active, or created_at
INSERT INTO products (product_id, product_name, category_id, price, cost_price, sku)
VALUES (201, 'Wireless Keyboard', 3, 49.99, 22.00, 'SKU-WK-201');

-- The row is stored as: stock_qty=0, is_active=1, created_at=current timestamp
SELECT p.product_id, p.product_name, p.stock_qty, p.is_active, p.created_at
FROM products p
WHERE p.product_id = 201;

Types of DEFAULT Values

You can use literals, expressions, and certain built-in functions as default values.

-- Literal defaults: fixed values that never change
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    customer_id     INTEGER NOT NULL,
    order_date      TEXT    NOT NULL DEFAULT (DATE('now')),
    status          TEXT    NOT NULL DEFAULT 'Pending',
    discount_amt    REAL    NOT NULL DEFAULT 0.00,
    tax_amt         REAL    NOT NULL DEFAULT 0.00,
    shipping_amt    REAL    NOT NULL DEFAULT 0.00,
    total_amount    REAL    NOT NULL DEFAULT 0.00
);

-- Timestamp defaults: auto-record when the row was created
CREATE TABLE customers (
    customer_id  INTEGER PRIMARY KEY,
    first_name   TEXT NOT NULL,
    last_name    TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE,
    loyalty_tier TEXT NOT NULL DEFAULT 'Bronze',
    created_at   TEXT NOT NULL DEFAULT (DATETIME('now'))
);

-- Payment defaults: status starts as Pending
CREATE TABLE payments (
    payment_id   INTEGER PRIMARY KEY,
    order_id     INTEGER NOT NULL,
    payment_date TEXT    NOT NULL DEFAULT (DATETIME('now')),
    amount       REAL    NOT NULL,
    method       TEXT    NOT NULL,
    status       TEXT    NOT NULL DEFAULT 'Pending'
);

Key idea: DEFAULT only fires when the column is completely absent from the INSERT statement. If you explicitly insert NULL into a NOT NULL DEFAULT column, the database raises a constraint error. The default does not rescue an explicit NULL; it only fills in a missing column.

DEFAULT and NOT NULL Together

The most common pattern is pairing DEFAULT with NOT NULL. This combination means: the column must always have a value, and if the insert does not supply one, use this fallback.

-- Realistic employees table with combined constraints
CREATE TABLE employees (
    employee_id  INTEGER PRIMARY KEY,
    first_name   TEXT NOT NULL,
    last_name    TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE,
    role         TEXT NOT NULL,
    department   TEXT NOT NULL,
    hire_date    TEXT NOT NULL DEFAULT (DATE('now')),
    salary       REAL NOT NULL DEFAULT 0.00,
    manager_id   INTEGER
);

hire_date is required but defaults to today if not supplied. salary is required but defaults to zero, which a subsequent UPDATE can correct. manager_id is nullable because the top-level employee has no manager.

-- Check which employees were inserted without an explicit hire_date
-- They would all share the same date if defaults fired on the same day
SELECT e.employee_id, e.first_name, e.last_name, e.hire_date
FROM employees e
WHERE e.hire_date = DATE('now')
ORDER BY e.employee_id;

Querying Default-Reliant Columns

Defaults affect what ends up in your data. When you query tables that rely heavily on defaults, you should know which columns were explicitly set and which were filled in automatically.

-- Find orders that kept the default 'Pending' status and were never updated
SELECT o.order_id, o.customer_id, o.order_date, o.status, o.total_amount
FROM orders o
WHERE o.status = 'Pending'
ORDER BY o.order_date DESC
LIMIT 20;

-- Find products that still have zero stock (default was never updated)
SELECT p.product_id, p.product_name, p.stock_qty, p.is_active
FROM products p
WHERE p.stock_qty = 0
  AND p.is_active = 1
ORDER BY p.product_name;

-- Find customers who were assigned the default Bronze loyalty tier
SELECT c.customer_id, c.first_name, c.last_name, c.loyalty_tier, c.created_at
FROM customers c
WHERE c.loyalty_tier = 'Bronze'
ORDER BY c.created_at DESC
LIMIT 15;

Overriding a DEFAULT Value

When you do supply a value in your INSERT, it replaces the default entirely.

-- Explicitly override the default status and loyalty_tier
INSERT INTO customers (customer_id, first_name, last_name, email, loyalty_tier, created_at)
VALUES (501, 'Riya', 'Nair', 'riya@example.com', 'Gold', '2024-01-15 09:00:00');

-- loyalty_tier is 'Gold', not the default 'Bronze'
SELECT c.customer_id, c.first_name, c.loyalty_tier
FROM customers c
WHERE c.customer_id = 501;

Common Mistakes

-- wrong
INSERT INTO products (product_id, product_name, category_id, price, cost_price, sku, is_active)
VALUES (202, 'USB Hub', 3, 19.99, 8.00, 'SKU-UH-202', NULL);

-- correct
INSERT INTO products (product_id, product_name, category_id, price, cost_price, sku)
VALUES (202, 'USB Hub', 3, 19.99, 8.00, 'SKU-UH-202');

Passing NULL explicitly overrides the DEFAULT and violates the NOT NULL constraint. To use the default, omit the column from the INSERT entirely rather than passing NULL.

---

-- wrong
UPDATE orders SET status = DEFAULT WHERE order_id = 55;

-- correct
UPDATE orders SET status = 'Pending' WHERE order_id = 55;

DEFAULT is not a valid keyword in an UPDATE statement in SQLite. If you want to reset a column to its default value, write the literal value explicitly.

---

-- wrong
CREATE TABLE payments (
    payment_id  INTEGER PRIMARY KEY,
    status      TEXT DEFAULT Pending
);

-- correct
CREATE TABLE payments (
    payment_id  INTEGER PRIMARY KEY,
    status      TEXT DEFAULT 'Pending'
);

String default values must be quoted. An unquoted word like Pending is interpreted as a column reference or keyword and will cause a syntax error.

Quick Recap

DEFAULT supplies a fallback value when an INSERT omits a column entirely. It does not rescue an explicit NULL passed in the statement.
Pair DEFAULT with NOT NULL for columns that must always have a value but have a sensible starting point, like status, stock_qty, is_active, or created_at.
In SQLite, use DEFAULT (DATETIME('now')) or DEFAULT (DATE('now')) with parentheses for timestamp and date defaults. String and numeric literals do not need parentheses.

Exercise 1 Easy

Write a query that returns all products where stock_qty is still at its default value of 0 and is_active is 1. Show product_id, product_name, stock_qty, and is_active. Sort by product_name ascending.

Hint: Filter with WHERE p.stock_qty = 0 AND p.is_active = 1. No joins needed.

Solution

SELECT p.product_id, p.product_name, p.stock_qty, p.is_active FROM products p WHERE p.stock_qty = 0 AND p.is_active = 1 ORDER BY p.product_name ASC;

Exercise 2 Medium

Write a query that finds all customers with a loyalty_tier of 'Bronze' who have also placed at least one order. Show customer_id, first_name, last_name, loyalty_tier, and a count of their orders as order_count. Sort by order_count descending.

Hint: Join customers and orders on customer_id. Filter by loyalty_tier = 'Bronze' in WHERE. Use GROUP BY and HAVING COUNT(o.order_id) >= 1.

Solution

SELECT c.customer_id, c.first_name, c.last_name, c.loyalty_tier, COUNT(o.order_id) AS order_count FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.loyalty_tier = 'Bronze' GROUP BY c.customer_id, c.first_name, c.last_name, c.loyalty_tier HAVING COUNT(o.order_id) >= 1 ORDER BY order_count DESC;

Exercise 3 Hard

For each order status, calculate the total number of orders, the total revenue as total_revenue rounded to 2 decimal places, and the average order value as avg_order_value rounded to 2 decimal places. Include all statuses. Then filter the results to only show statuses where the average order value is above the overall average order value across all orders. Use a subquery for the overall average. Sort by total_revenue descending.

Hint: Use a subquery (SELECT AVG(total_amount) FROM orders) in your HAVING clause. Group by o.status and compute SUM and AVG of total_amount.

Solution

SELECT o.status, COUNT(o.order_id) AS total_orders, ROUND(SUM(o.total_amount), 2) AS total_revenue, ROUND(AVG(o.total_amount), 2) AS avg_order_value FROM orders o GROUP BY o.status HAVING AVG(o.total_amount) > (SELECT AVG(total_amount) FROM orders) ORDER BY total_revenue DESC;

Frequently Asked Questions

What does the DEFAULT constraint do in SQL?

It sets a fallback value for a column when an INSERT statement does not provide one. The database uses the default automatically so the column is never left empty unintentionally.

What is the difference between DEFAULT and NOT NULL in SQL?

NOT NULL rejects the insert if no value is provided. DEFAULT supplies a value automatically so the insert succeeds without the caller needing to specify one. They are often used together.

Can you use a function as a DEFAULT value in SQL?

It depends on the database. SQLite supports CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME as default expressions. Most other databases support similar functions plus custom expressions.

Can you override a DEFAULT value in SQL?

Yes. If you supply a value in your INSERT statement, it replaces the default. The default only applies when the column is omitted from the INSERT entirely.