MySQL DEFAULT Constraint

⚙️ MySQL DEFAULT Constraint 

The DEFAULT constraint in MySQL is used to assign a default value to a column when no value is provided during INSERT.

👉 It helps maintain data consistency and reduces NULL values.

1️⃣ What is DEFAULT Constraint?

  • Automatically inserts a predefined value

  • Applied when:

    • Column value is not specified

    • INSERT omits the column

📌 Works with numbers, strings, dates, booleans

2️⃣ Why Use DEFAULT?

✔ Prevents NULL values
✔ Ensures consistent data
✔ Simplifies INSERT statements
✔ Very useful in real applications

3️⃣ Basic Syntax


4️⃣ Simple Example


Insert Without City


Result

id | name | city
1 | Amit | Kolkata

✔ city automatically gets default value

5️⃣ DEFAULT with Numeric Column


📌 price becomes 100

6️⃣ DEFAULT with Date & Time ⭐


✔ order_date gets current date

7️⃣ DEFAULT with CURRENT_TIMESTAMP ⭐


📌 Very common in audit & logging tables

8️⃣ Adding DEFAULT Using ALTER TABLE


👉 (MySQL 8+)

9️⃣ Removing DEFAULT Constraint


🔟 DEFAULT vs NULL ⭐ (Interview)

Feature DEFAULT NULL
Automatic value ✔ Yes ❌ No
Ensures consistency ✔ ❌
Recommended ✔ Yes ❌ Avoid

1️⃣1️⃣ Common Mistakes ❌

❌ Using DEFAULT with incompatible data types
❌ Expecting DEFAULT to apply when NULL is explicitly inserted
❌ Forgetting parentheses in functions

Example ❌:


📌 Interview Questions & MCQs (Very Important)

Q1. What is the purpose of DEFAULT constraint?

A) Prevent duplicates
B) Assign automatic value
C) Create index
D) Validate data

✅ Answer: B

Q2. When is DEFAULT value used?

A) Always
B) Only when column is omitted
C) When NULL is inserted
D) During UPDATE

✅ Answer: B

Q3. Which keyword sets current date as default?

A) NOW
B) CURRENT
C) CURDATE()
D) DATE()

✅ Answer: C

Q4. Which data type commonly uses CURRENT_TIMESTAMP?

A) VARCHAR
B) INT
C) DATE
D) TIMESTAMP

✅ Answer: D

Q5. Will DEFAULT apply if NULL is explicitly inserted?

A) Yes
B) No

✅ Answer: B

Q6. How to remove DEFAULT constraint?

A) DROP DEFAULT
B) REMOVE DEFAULT
C) ALTER … DROP DEFAULT
D) DELETE DEFAULT

✅ Answer: C

🔥 Real-Life Use Cases

✔ Default city / country
✔ Default status (active)
✔ Auto date & time entries
✔ Initial price or quantity

✅ Summary

  • DEFAULT assigns automatic values

  • Used when column is omitted in INSERT

  • Works with numbers, strings, dates

  • CURRENT_TIMESTAMP is very common

  • Very important for SQL interviews & exams

You may also like...