Teaching Your Database to Say “YES” and “NO” with DEFAULT and CHECK Constraints
Imagine your database is a school
- Students = Rows
- Register = Table
- Rules = Constraints
If there are no rules, students can:
- Skip their name
- Write age as
-5 - Enter marks as
9999
That would be chaos
So we use constraints to keep data clean, safe, and sensible.
Today, we learn two important rules:
- DEFAULT Constraint – “If you forget, I’ll fill it for you.”
- CHECK Constraint – “I will only allow good values”
We’ll learn this using MySQL.
What Is a Constraint?
A constraint is a rule applied to a column to control what data can go inside it.
Think of it as:
Traffic rules for your database roads
DEFAULT Constraint – The Helpful Friend
What Is DEFAULT?
The DEFAULT constraint gives a column a predefined value
Only when you don’t provide one
Example
If you forget your school uniform, the school gives you a spare one.
That’s DEFAULT
Real Example
Table: users
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
country VARCHAR(30) DEFAULT 'India'
);
Insert without country
INSERT INTO users (id, name)
VALUES (1, 'Ravi');
Result
| id | name | country |
|---|---|---|
| 1 | Ravi | India |
Magic!
Because you didn’t give country, MySQL used DEFAULT ‘India’.
DEFAULT with Date
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE DEFAULT (CURRENT_DATE)
);
INSERT INTO orders (order_id)
VALUES (101);
Today’s date is added automatically.
When to Use DEFAULT
✔ Created date
✔ Status (ACTIVE, PENDING)
✔ Country
✔ Role (USER)
CHECK Constraint – The Strict Teacher
What Is CHECK?
The CHECK constraint rejects bad data.
“I will NOT allow this value!”
Example
- Age must be greater than 0
- Marks must be 0 to 100
If you try 150 → Teacher says NO!
Real Example: Age Rule
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT CHECK (age >= 5)
);
Valid Insert
INSERT INTO students VALUES (1, 'Aman', 10);
Invalid Insert
INSERT INTO students VALUES (2, 'Riya', 3);
Error: CHECK constraint failed
CHECK with Salary
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
salary DECIMAL(10,2),
CHECK (salary >= 10000)
);
No underpaid employees allowed
Combining DEFAULT + CHECK (Power Combo)
CREATE TABLE accounts (
acc_id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'ACTIVE',
balance DECIMAL(10,2) CHECK (balance >= 0)
);
✔ Status auto-filled
✔ Balance never negative
Perfect database behavior
Important MySQL Note (Very Interview-Important!)
CHECK constraints are fully enforced in MySQL 8.0.16+
Older versions:
- CHECK was parsed but ignored
Always mention this in interviews!
Practice Questions
Question 1
Create a table products where:
- price must be greater than 0
- stock default value is 10
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0),
stock INT DEFAULT 10
);
Question 2
Insert a product without a stock value. What happens?
Stock becomes 10 because of DEFAULT.
Question 3
Can age be -1 if CHECK is applied?
No. CHECK constraint blocks it.
Interview Questions & Answers (Real-World Ready)
Q1: What is a DEFAULT constraint?
Answer:
DEFAULT constraint assigns a predefined value to a column when no value is provided during insert.
Q2: What is a CHECK constraint?
Answer:
CHECK constraint ensures that column values satisfy a given condition.
Q3: Can DEFAULT and CHECK be used together?
Answer:
Yes. DEFAULT provides a value, and CHECK validates it.
Q4: Is CHECK constraint enforced in MySQL?
Answer:
Yes, from MySQL 8.0.16 onwards. Earlier versions ignored it.
Q5: Give a real-world use case for CHECK.
Answer:
- Age ≥ 18
- Salary ≥ minimum wage
- Rating between 1 and 5
Q6: Can CHECK reference another column?
Answer:
Yes.
CHECK (end_date > start_date)
Final Summary
| Constraint | What it Does | Simple Meaning |
|---|---|---|
| DEFAULT | Adds value automatically | “I’ll help you if you forget” |
| CHECK | Stops bad data | “Only good values allowed” |
A database without constraints is like a playground with no rules — fun at first, chaos later.
Using DEFAULT and CHECK makes your database:
- Safer
- Cleaner
- Smarter