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.

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

 Why Use DEFAULT?

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

Basic Syntax

Simple Example

• city automatically gets default value

DEFAULT with Numeric Column

• price becomes 100

DEFAULTS with Date & Time

• order_date gets current date

DEFAULT with CURRENT_TIMESTAMP

•  Very common in audit & logging tables

 Adding DEFAULT Using ALTER TABLE

•  (MySQL 8+)

 Removing DEFAULT Constraint

 DEFAULT vs NULL (Interview)

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