CodeCapsule

SQL DEFAULT constraint

by

Here is a clear, practical, and complete guide to the SQL DEFAULT constraint, including syntax, behavior, examples, and DB-specific differences.

✅ What the DEFAULT Constraint Does

A DEFAULT constraint automatically assigns a value to a column when no value is provided during an INSERT.

Useful for:

  • Timestamps

  • Status fields

  • Boolean flags

  • Counters

  • System-generated values

🔹 Basic Syntax (CREATE TABLE)

CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
status VARCHAR(20) DEFAULT 'active'
);

If status is omitted in an insert, it becomes 'active'.

🔹 Example: DEFAULT with Timestamp

PostgreSQL:

created_at TIMESTAMP DEFAULT NOW()

MySQL:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

SQL Server:

created_at DATETIME DEFAULT GETDATE()

🔥 DEFAULT on Numeric Fields

CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2),
stock INT DEFAULT 0
);

🔥 DEFAULT on Boolean Fields

is_active BOOLEAN DEFAULT TRUE

🔥 DEFAULT with Expressions (Engine-Dependent)

PostgreSQL supports expressions:

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + INTERVAL '1 day'

MySQL supports some expressions:

discount_expiration DATETIME DEFAULT (NOW() + INTERVAL 7 DAY)

SQL Server requires parents for functions:

updated_at DATETIME DEFAULT (GETDATE())

🔹 DEFAULT in ALTER TABLE

PostgreSQL:

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

MySQL:

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

SQL Server:

ALTER TABLE users
ADD CONSTRAINT df_status DEFAULT 'active' FOR status;

(SQL Server requires naming a default constraint when added later.)

🔹 Remove DEFAULT Constraint

PostgreSQL:

ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

MySQL:

ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

SQL Server:

ALTER TABLE users
DROP CONSTRAINT df_status;

📌 Real-World Examples

1. Default user status

status VARCHAR(20) DEFAULT 'pending'

2. Auto-timestamp creation date

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3. Default order quantity

quantity INT DEFAULT 1

4. Default JSON structure (PostgreSQL)

metadata JSONB DEFAULT '{}'

🧠 Important Behavior Notes

✔ DEFAULT applies only when the column is omitted — not when NULL is explicitly inserted.
Example:

INSERT INTO users (id, name, status) VALUES (1, 'Bob', NULL);

→ status becomes NULL, not the default.

✔ Use NOT NULL + DEFAULT to guarantee a value:

status VARCHAR(20) NOT NULL DEFAULT 'active';

✔ DEFAULT does not retroactively update existing rows.

✔ Triggers, functions, and computed columns may override defaults depending on DB engine.

🏎 Best Practices

✔ Always combine DEFAULT with NOT NULL for required fields
✔ Use default timestamps for audit fields (created_at)
✔ For boolean flags, set defaults (is_active DEFAULT TRUE)
✔ Name default constraints in SQL Server for easier maintenance
✔ Avoid complex expressions that hurt readability
✔ Ensure DEFAULT values reflect business logic

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *