🧱 What are Constraints in DBMS?
Constraints = Rules or conditions that we apply on table columns to ensure valid, correct,
and consistent data.
Soch le constraints = bodyguard 😎
Jo galat data ko andar nahi aane deta table ke andar.
🔢 Types of Constraints (Sorted + Friendly Way):
🔢 🔒 Constraint 📘 Description 💡 Example
#
1️⃣ NOT NULL Column cannot be empty name VARCHAR(50) NOT
NULL
2️⃣ UNIQUE Value must be unique, email VARCHAR(100)
duplicates not allowed UNIQUE
3️⃣ PRIMARY KEY Unique + Not Null, used to roll_no NUMBER
uniquely identify each row PRIMARY KEY
4️⃣ FOREIGN KEY Links to another table’s student_id
primary key, relationship REFERENCES
banata hai student(roll_no)
5️⃣ CHECK Validates a condition on data age NUMBER CHECK
(age >= 18)
6️⃣ DEFAULT Sets a default value if user status VARCHAR2(10)
doesn’t provide DEFAULT 'active'
7️⃣ AUTO INCREMENT Automatically increases id NUMBER GENERATED
(Oracle: sequence use value (MySQL supports ALWAYS AS IDENTITY
hota hai) directly)
✅ Example Table with All Constraints
CREATE TABLE student (
roll_no NUMBER PRIMARY KEY, -- unique & not null
name VARCHAR2(50) NOT NULL, -- name must be filled
email VARCHAR2(100) UNIQUE, -- no duplicate emails
age NUMBER CHECK (age >= 18), -- minimum age 18
branch VARCHAR2(10) DEFAULT 'CSE', -- default branch
mentor_id NUMBER, -- foreign key
CONSTRAINT fk_mentor FOREIGN KEY (mentor_id)
REFERENCES faculty(faculty_id) -- relationship with another table
);
💭 Real-Life Analogy:
Constraint Real-Life Example
NOT NULL Naam to dena hi padega form me
UNIQUE Aadhaar number har kisi ka alag hota hai
PRIMARY KEY Roll number sabka unique & must
FOREIGN KEY Har student ka ek assigned mentor
CHECK Age 18+ hona chahiye license ke liye
DEFAULT Agar koi option select nahi kiya, toh default lagao
🎯 Why Constraints Matter?
● Protect data from being wrong
● Maintain relationships between tables
● Enforce business rules
● Make the database powerful & safe
Example -
-- Enable foreign key support in SQLite
PRAGMA foreign_keys = ON;
-- Drop tables if they exist
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS faculty;
-- Create faculty table
CREATE TABLE faculty (
faculty_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT
);
-- Insert faculty data
INSERT INTO faculty (faculty_id, name, department)
VALUES (1, 'Dr. Mehta', 'CSE');
INSERT INTO faculty (faculty_id, name, department)
VALUES (2, 'Dr. Sharma', 'ECE');
-- Create student table with constraints
CREATE TABLE student (
roll_no INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER CHECK (age >= 18),
branch TEXT DEFAULT 'CSE',
mentor_id INTEGER,
FOREIGN KEY (mentor_id) REFERENCES faculty(faculty_id)
);
-- Valid insert (all constraints ok)
INSERT INTO student (roll_no, name, email, age, mentor_id)
VALUES (101, 'Prafull Narang', 'prafull@example.com', 22, 1);
-- Valid insert (branch not provided, so default 'CSE' applies)
INSERT INTO student (roll_no, name, email, age, mentor_id)
VALUES (102, 'Aarav Singh', 'aarav@example.com', 21, 2);
-- View records
SELECT * FROM faculty;
SELECT * FROM student;
Output
