What is the DEFAULT Constraint?
The DEFAULT constraint specifies a value that is automatically
inserted into a column when an INSERT statement does not provide
a value for that column.
DEFAULT values are useful for fields such as status, quantity, country, timestamps, and other values that commonly have the same starting value.
Basic DEFAULT Syntax
A default value is defined after the column’s data type using the
DEFAULT keyword.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INTEGER DEFAULT 10
);
INSERT INTO products (product_id, product_name)
VALUES (1, 'Notebook');
INSERT INTO products (product_id, product_name, quantity)
VALUES (2, 'Pen Set', 25);
SELECT *
FROM products
ORDER BY product_id;DEFAULT Text Value
A DEFAULT value can be a text string. Text values should be enclosed in single quotes.
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
task_id INTEGER PRIMARY KEY,
task_name TEXT NOT NULL,
status TEXT DEFAULT 'Pending'
);
INSERT INTO tasks (task_id, task_name)
VALUES
(101, 'Prepare report'),
(102, 'Review database'),
(103, 'Update documentation');
INSERT INTO tasks (task_id, task_name, status)
VALUES
(104, 'Publish tutorial', 'Completed');
SELECT *
FROM tasks
ORDER BY task_id;DEFAULT Numeric Value
DEFAULT can also provide an initial numeric value when a column is omitted from an INSERT statement.
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
item_id INTEGER PRIMARY KEY,
item_name TEXT NOT NULL,
stock INTEGER DEFAULT 0,
reorder_level INTEGER DEFAULT 5
);
INSERT INTO inventory (item_id, item_name)
VALUES
(1, 'USB Cable'),
(2, 'Wireless Mouse');
INSERT INTO inventory (item_id, item_name, stock)
VALUES
(3, 'Keyboard', 20);
SELECT *
FROM inventory
ORDER BY item_id;DEFAULT with Multiple Columns
A table can contain multiple columns with different DEFAULT values. Each omitted column receives its own default value.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT NOT NULL,
department TEXT DEFAULT 'General',
salary REAL DEFAULT 30000,
active INTEGER DEFAULT 1
);
INSERT INTO employees (employee_id, employee_name)
VALUES
(1, 'Aarav'),
(2, 'Meera');
SELECT *
FROM employees
ORDER BY employee_id;DEFAULT with NOT NULL
DEFAULT and NOT NULL can be used together. When the column
is omitted from INSERT, its DEFAULT value is used. The resulting value
satisfies the NOT NULL requirement.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'New',
quantity INTEGER NOT NULL DEFAULT 1
);
INSERT INTO orders (order_id, customer_name)
VALUES
(1001, 'Riya'),
(1002, 'Kabir');
INSERT INTO orders (order_id, customer_name, status, quantity)
VALUES
(1003, 'Nisha', 'Processing', 3);
SELECT *
FROM orders
ORDER BY order_id;DEFAULT with CURRENT_TIMESTAMP
SQLite supports CURRENT_TIMESTAMP as a DEFAULT value.
It stores the current date and time in UTC when a row is inserted.
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
message_id INTEGER PRIMARY KEY,
message_text TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO messages (message_id, message_text)
VALUES
(1, 'Welcome to the SQL tutorial'),
(2, 'Learning DEFAULT values');
SELECT *
FROM messages
ORDER BY message_id;DEFAULT for a Date Value
A fixed date can also be used as a DEFAULT value when appropriate. Here the date is stored as text in ISO format.
DROP TABLE IF EXISTS events;
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
event_name TEXT NOT NULL,
event_date TEXT DEFAULT '2026-12-01'
);
INSERT INTO events (event_id, event_name)
VALUES
(1, 'Technology Workshop'),
(2, 'Database Seminar');
INSERT INTO events (event_id, event_name, event_date)
VALUES
(3, 'Robotics Exhibition', '2027-01-15');
SELECT *
FROM events
ORDER BY event_id;DEFAULT and Explicit NULL
A DEFAULT value is normally used when a column is omitted from the
INSERT statement. If NULL is explicitly supplied and the
column permits NULL, the explicit NULL is stored instead of the DEFAULT.
DROP TABLE IF EXISTS profiles;
CREATE TABLE profiles (
profile_id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
nickname TEXT DEFAULT 'Guest'
);
INSERT INTO profiles (profile_id, username)
VALUES
(1, 'user_one');
INSERT INTO profiles (profile_id, username, nickname)
VALUES
(2, 'user_two', NULL);
SELECT *
FROM profiles
ORDER BY profile_id;DEFAULT with INSERT
The most common use of DEFAULT is to omit a column during INSERT and allow the database to supply its predefined value.
DROP TABLE IF EXISTS support_requests;
CREATE TABLE support_requests (
request_id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
priority TEXT DEFAULT 'Normal',
status TEXT DEFAULT 'Open'
);
INSERT INTO support_requests (request_id, customer)
VALUES
(501, 'Aditi'),
(502, 'Rohan');
INSERT INTO support_requests (request_id, customer, priority)
VALUES
(503, 'Sana', 'High');
SELECT *
FROM support_requests
ORDER BY request_id;DEFAULT with UPDATE
DEFAULT values are applied during INSERT. An UPDATE does not
automatically reapply a column’s DEFAULT value. However, SQLite
supports inserting a row using the DEFAULT VALUES form
when all columns have suitable defaults.
DROP TABLE IF EXISTS system_logs;
CREATE TABLE system_logs (
log_id INTEGER PRIMARY KEY,
message TEXT DEFAULT 'System started',
severity TEXT DEFAULT 'Info'
);
INSERT INTO system_logs DEFAULT VALUES;
INSERT INTO system_logs (log_id, message, severity)
VALUES
(2, 'Database connected', 'Success');
SELECT *
FROM system_logs
ORDER BY log_id;Common DEFAULT Values
| Type | Example | Use |
|---|---|---|
| Number | DEFAULT 0 | Initial quantity or counter |
| Text | DEFAULT ‘Pending’ | Initial status |
| Date | DEFAULT ‘2026-12-01’ | Default date |
| Timestamp | DEFAULT CURRENT_TIMESTAMP | Record creation time |
| Boolean-style value | DEFAULT 1 | Enabled/active state |
Advantages of DEFAULT
- Automatically supplies commonly used values.
- Reduces the amount of data that must be supplied during INSERT.
- Helps keep records consistent.
- Can provide initial statuses and quantities.
- Can automatically record the current timestamp.
- Works well with NOT NULL columns when a valid default is provided.
Best Practices
- Use DEFAULT for values that have a sensible common starting value.
- Use NOT NULL with DEFAULT when the column should always contain a value.
- Use CURRENT_TIMESTAMP when a creation timestamp is required.
- Choose default values that make sense for the application’s data.
- Remember that DEFAULT is applied when a column is omitted, not when an explicit NULL is supplied.
- Do not use DEFAULT as a replacement for data validation constraints such as CHECK.
The DEFAULT constraint makes database design easier by automatically supplying appropriate values whenever an INSERT statement does not provide them.
🧪 Test Your SQL Code
Edit the SQL code on the left and click “Run Code” to see the result on the right.
Click “Run Code” to see the result here.