Constraints are like rules we put on a table’s columns to control what kind of data can go inside.
Think of it like traffic rules:
- They guide how data should be stored.
- They prevent wrong entries.
- They keep the database clean and consistent.
Types of Constraints (with Simple Examples)
NOT NULL
Means the column cannot be empty.
Like in college admission form → Name is mandatory.
Example:
sql
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);You must enter a name, otherwise error.
UNIQUE
Ensures all values in a column are different.
Like in school → No two students can have the same roll number.
Example:
sql
CREATE TABLE students (
email VARCHAR(100) UNIQUE
);No two students can use the same email.
PRIMARY KEY
Combination of NOT NULL + UNIQUE.
- It uniquely identifies each row.
- Example: Aadhar Number or Roll Number.
Example:
sql
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50)
);
FOREIGN KEY
Connects one table with another.
Like → Student ID in the Marks table refers to Student ID in Students table.
Example:
sql
CREATE TABLE marks (
mark_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
CHECK
Used to put a condition on column values.
Like in a form → Age must be above 18.
Example:
sql
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);
If you try to insert age = 15, it will throw an error.
DEFAULT
If no value is given, SQL will use a default value.
Like in college → If religion is not mentioned, set it as “General” by default.
Example:
sql
CREATE TABLE students (
id INT PRIMARY KEY,
city VARCHAR(50) DEFAULT 'Delhi'
);
If city is not given, it will automatically store “Delhi”.
AUTO_INCREMENT
Automatically generates a new number for each row.
Like → Roll numbers are given automatically in sequence.
Example:
sql
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
- First student → id = 1
- Second student → id = 2
Real-Life Analogy
- NOT NULL → Name field in exam form (must fill).
- UNIQUE → Mobile number (no duplicate).
- PRIMARY KEY → Roll number (unique + mandatory).
- FOREIGN KEY → Linking Aadhaar with PAN card.
- CHECK → Age must be 18+ for voter ID.
- DEFAULT → If no city is given, default = Delhi.
- AUTO_INCREMENT → Roll numbers given 1, 2, 3 automatically.
Super Simple Memory Trick
Constraints = Rules for data entry
- NOT NULL → Must fill
- UNIQUE → No duplicates
- PRIMARY KEY → Unique identity card
- FOREIGN KEY → Reference/link
- CHECK → Condition
- DEFAULT → Auto value if not given
- AUTO_INCREMENT → Auto numbering