SQL DEFAULT – Auto-Fill Column Values on Insert
Introduction – What is SQL DEFAULT?
The DEFAULT constraint in SQL is used to automatically assign a predefined value to a column when no value is provided during INSERT. It helps ensure data consistency and simplify data entry.
In this guide, you’ll learn:
- How to define default values
- Use with various data types
- Best practices and platform behavior
1. DEFAULT Syntax in CREATE TABLE
CREATE TABLE users (
id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'active',
created_at DATE DEFAULT CURRENT_DATE
);
If no status or created_at is provided, default values are used.
2. Add DEFAULT to Existing Table
ALTER TABLE products
ALTER COLUMN quantity SET DEFAULT 0;
-- MySQL (different syntax)
ALTER TABLE products
MODIFY quantity INT DEFAULT 0;
Adds a default value to existing columns.
3. DEFAULT with Various Data Types
| Column Type | Example Default | Notes |
|---|---|---|
INT | 0, 1 | Common for flags or counters |
VARCHAR | 'pending' | Useful for status fields |
DATE/TIMESTAMP | CURRENT_DATE, NOW() | Auto-timestamps |
BOOLEAN | TRUE, FALSE | Used in logical columns |
4. Behavior Notes
DEFAULTis applied only when column value is omitted- It does not override
NULLif explicitly passed - Can be combined with
NOT NULLfor stricter enforcement
Example:
INSERT INTO users (id) VALUES (1); -- uses defaults for other columns
Best Practices
| Recommended | Avoid This |
|---|---|
| Use DEFAULT for audit, status, flags | Hardcoding values in every query |
Combine with NOT NULL if value is mandatory | Relying only on app logic for defaults |
| Use date/time defaults for created/updated fields | Using static timestamps unnecessarily |
Summary – Recap & Next Steps
The DEFAULT constraint is ideal for automating data population with expected initial values. It simplifies schema usage and improves consistency.
Key Takeaways:
- DEFAULT fills in values when none are provided
- Applies to INSERTs only (not UPDATEs)
- Combine with
NOT NULL,CHECK, orAUTO_INCREMENT
Real-World Relevance:
Used in flags (active, is_admin), timestamps, quantity fields, and user status flows.
FAQ – SQL DEFAULT
What is the use of DEFAULT in SQL?
It automatically assigns a value when none is provided during insert.
Can I override a DEFAULT value?
Yes. Just specify a value explicitly in your query.
Can DEFAULT be used with all column types?
Yes, but behavior depends on the DBMS for types like TIMESTAMP or BOOLEAN.
Does DEFAULT apply on UPDATE?
No. It only applies during INSERT.
Share Now :
