SQL - DEFAULT Constraint
• DEFAULT constraints provide automatic fallback values when INSERT or UPDATE statements omit column values, reducing application-side logic and ensuring data consistency
Key Insights
• DEFAULT constraints provide automatic fallback values when INSERT or UPDATE statements omit column values, reducing application-side logic and ensuring data consistency • Modern SQL databases support complex DEFAULT expressions including functions, sequences, and computed values—not just static literals • Strategic use of DEFAULT constraints improves data integrity, simplifies queries, and centralizes business rules at the database layer rather than scattering them across application code
Understanding DEFAULT Constraints
A DEFAULT constraint assigns an automatic value to a column when no explicit value is provided during INSERT operations. This constraint operates at the database level, executing before the row is written to disk. Unlike triggers or application logic, DEFAULT constraints are declarative and execute with minimal overhead.
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'active',
login_count INT DEFAULT 0
);
-- Inserting without specifying default columns
INSERT INTO users (user_id, username)
VALUES (1, 'alice');
-- Result: created_at gets current timestamp, status gets 'active', login_count gets 0
The database engine evaluates DEFAULT constraints only when the column is absent from the INSERT statement. Explicitly passing NULL bypasses the DEFAULT constraint unless combined with a NOT NULL constraint.
-- This inserts NULL, not the default value
INSERT INTO users (user_id, username, status)
VALUES (2, 'bob', NULL);
-- This uses the default value
INSERT INTO users (user_id, username)
VALUES (3, 'charlie');
Adding and Modifying DEFAULT Constraints
You can add DEFAULT constraints to existing tables using ALTER TABLE statements. The syntax varies slightly between database systems, but the concept remains consistent.
-- PostgreSQL and MySQL syntax
ALTER TABLE users
ALTER COLUMN last_login SET DEFAULT CURRENT_TIMESTAMP;
-- SQL Server syntax
ALTER TABLE users
ADD CONSTRAINT DF_users_last_login
DEFAULT CURRENT_TIMESTAMP FOR last_login;
-- Removing a DEFAULT constraint
ALTER TABLE users
ALTER COLUMN last_login DROP DEFAULT;
Named constraints provide better control and debugging capabilities:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE CONSTRAINT df_order_date DEFAULT CURRENT_DATE,
shipping_method VARCHAR(50) CONSTRAINT df_shipping DEFAULT 'standard',
priority_level INT CONSTRAINT df_priority DEFAULT 3
);
-- Drop specific constraint by name
ALTER TABLE orders DROP CONSTRAINT df_shipping;
Function-Based DEFAULT Values
Modern databases support function calls and expressions in DEFAULT constraints, enabling sophisticated automatic value generation.
-- UUID generation (PostgreSQL)
CREATE TABLE sessions (
session_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '24 hours')
);
-- Computed defaults
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
sku VARCHAR(50) DEFAULT UPPER(SUBSTRING(MD5(RANDOM()::TEXT), 1, 10)),
price DECIMAL(10,2),
discount_price DECIMAL(10,2) DEFAULT 0.00,
margin DECIMAL(5,2) DEFAULT 0.30
);
Database-specific functions unlock powerful capabilities:
-- SQL Server: NEWID() for GUIDs
CREATE TABLE documents (
doc_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
title VARCHAR(200) NOT NULL,
created_date DATETIME DEFAULT GETDATE()
);
-- MySQL: AUTO_INCREMENT with timestamp
CREATE TABLE audit_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
event_type VARCHAR(50),
event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
event_data JSON DEFAULT (JSON_OBJECT())
);
Sequences and Identity Columns
While AUTO_INCREMENT and IDENTITY columns are technically different from DEFAULT constraints, they serve similar purposes for automatic value generation.
-- PostgreSQL sequences with DEFAULT
CREATE SEQUENCE order_number_seq START 1000;
CREATE TABLE orders (
order_id INT DEFAULT nextval('order_number_seq') PRIMARY KEY,
customer_id INT NOT NULL,
order_number VARCHAR(20) DEFAULT ('ORD-' || nextval('order_number_seq')::TEXT)
);
-- SQL Server IDENTITY with explicit DEFAULT for other columns
CREATE TABLE invoices (
invoice_id INT IDENTITY(1,1) PRIMARY KEY,
invoice_number AS ('INV-' + RIGHT('00000' + CAST(invoice_id AS VARCHAR), 5)),
invoice_date DATE DEFAULT GETDATE(),
due_date AS DATEADD(DAY, 30, invoice_date)
);
DEFAULT with CHECK Constraints
Combining DEFAULT with CHECK constraints ensures both automatic values and validation rules work together:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) DEFAULT 'General',
salary DECIMAL(10,2) DEFAULT 50000.00,
hire_date DATE DEFAULT CURRENT_DATE,
CONSTRAINT chk_salary CHECK (salary >= 30000 AND salary <= 500000),
CONSTRAINT chk_department CHECK (department IN ('Engineering', 'Sales', 'General', 'HR'))
);
-- This succeeds: default department is valid
INSERT INTO employees (employee_id, name) VALUES (1, 'Alice');
-- This fails: default salary is valid, but explicit value violates CHECK
INSERT INTO employees (employee_id, name, salary)
VALUES (2, 'Bob', 20000);
Practical Patterns and Use Cases
Audit Trail Columns
CREATE TABLE customer_records (
record_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(100),
-- Audit fields with defaults
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
created_by VARCHAR(50) DEFAULT CURRENT_USER,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_by VARCHAR(50) DEFAULT CURRENT_USER,
version INT DEFAULT 1,
is_deleted BOOLEAN DEFAULT FALSE
);
Configuration Tables with Sensible Defaults
CREATE TABLE application_settings (
setting_id INT PRIMARY KEY,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value VARCHAR(500),
-- Metadata with defaults
is_enabled BOOLEAN DEFAULT TRUE,
category VARCHAR(50) DEFAULT 'general',
priority INT DEFAULT 50,
requires_restart BOOLEAN DEFAULT FALSE,
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
State Machine Defaults
CREATE TABLE workflow_tasks (
task_id INT PRIMARY KEY,
task_name VARCHAR(200) NOT NULL,
-- Workflow state with defaults
status VARCHAR(20) DEFAULT 'pending',
assigned_to INT DEFAULT NULL,
priority VARCHAR(10) DEFAULT 'normal',
retry_count INT DEFAULT 0,
max_retries INT DEFAULT 3,
next_retry_at TIMESTAMP DEFAULT NULL,
CONSTRAINT chk_status CHECK (status IN ('pending', 'in_progress', 'completed', 'failed')),
CONSTRAINT chk_priority CHECK (priority IN ('low', 'normal', 'high', 'critical'))
);
Performance Considerations
DEFAULT constraints execute during INSERT operations, so complex expressions can impact write performance. For high-throughput tables, benchmark different approaches:
-- Fast: Static value
CREATE TABLE events_fast (
event_id BIGINT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'new',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Slower: Complex computation
CREATE TABLE events_complex (
event_id BIGINT PRIMARY KEY,
partition_key VARCHAR(50) DEFAULT (
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM') || '-' ||
LPAD(FLOOR(RANDOM() * 1000)::TEXT, 3, '0')
),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
For expensive computations, consider moving logic to triggers or application code. DEFAULT constraints excel with simple, deterministic values that execute quickly.
Database-Specific Behaviors
Different databases handle DEFAULT constraints with subtle variations:
PostgreSQL supports the most sophisticated expressions, including subqueries in some contexts and extensive function support.
SQL Server requires named constraints for easier management and supports computed columns alongside DEFAULT constraints.
MySQL historically limited DEFAULT to literals and specific functions like CURRENT_TIMESTAMP, though recent versions expanded support.
Oracle uses DEFAULT with sequences through the DEFAULT ON NULL clause for handling explicit NULL values.
Always consult your database documentation for specific limitations and test DEFAULT behavior in your target environment before production deployment.