🧩 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

• DEFAULT is applied only when column value is omitted
• It does not override NULL if explicitly passed
• Can be combined with NOT NULL for 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, or AUTO_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 :