🔐 Database Constraints
Visual Explanation of Rules That Keep Database Data Correct
🌟 What is a Constraint?
Think of a database as a very organized register. A constraint is simply a rule that tells the database what data is allowed and what data is not allowed.
For example:
❌ A student should not have two identical Roll Numbers.
❌ A student's age should not be -5.
❌ A required Student Name should not be empty.
✅ A student's age can be 20.
Click the buttons below to see each rule visually.
Think of a database as a very organized register. A constraint is simply a rule that tells the database what data is allowed and what data is not allowed.
For example:
❌ A student should not have two identical Roll Numbers.
❌ A student's age should not be -5.
❌ A required Student Name should not be empty.
✅ A student's age can be 20.
Click the buttons below to see each rule visually.
📏 1. Domain Constraint
Easy Meaning:
A domain constraint specifies what type and range of values an attribute is allowed to contain.
Think: "What type of value is allowed here?"
A domain constraint specifies what type and range of values an attribute is allowed to contain.
Think: "What type of value is allowed here?"
| Student | Age |
|---|---|
| Rahul | 20 ✅ |
| Anita | 19 ✅ |
| Arjun | ABC ❌ |
| Riya | -10 ❌ |
For an Age column, we may decide:
Age must be a number.
Age must be greater than 0.
Therefore: 20 ✅
"ABC" ❌
-10 ❌
Age must be a number.
Age must be greater than 0.
Therefore: 20 ✅
"ABC" ❌
-10 ❌
🧠 Memory Trick:
DOMAIN = Allowed Type + Allowed Range
DOMAIN = Allowed Type + Allowed Range
🚫 2. NOT NULL Constraint
NOT NULL means a column must contain a value.
In simple words: "You cannot leave this field empty."
In simple words: "You cannot leave this field empty."
| Roll No | Name |
|---|---|
| 101 | Rahul ✅ |
| 102 | Anita ✅ |
| 103 | EMPTY ❌ |
1
Name is marked as NOT NULL.
2
A new student record is entered.
3
Name is left empty.
4
❌ Database rejects the record.
🔢 3. UNIQUE Constraint
UNIQUE ensures that no two records contain the same value
in that column.
Think: "Nobody can have the same value."
Think: "Nobody can have the same value."
| Student | |
|---|---|
| Rahul | rahul@gmail.com ✅ |
| Anita | anita@gmail.com ✅ |
| Arjun | rahul@gmail.com ❌ |
⚠️ The third record uses an email address that already exists.
Therefore the UNIQUE constraint rejects it.
Therefore the UNIQUE constraint rejects it.
🔑 4. Primary Key
A primary key uniquely identifies every record in a table.
Think of it as the unique ID card number of a record.
Think of it as the unique ID card number of a record.
| 🔑 Roll_No | Name | Course |
|---|---|---|
| 101 | Rahul | BCA |
| 102 | Anita | BCA |
| 101 ❌ | Arjun | BCA |
🔑 Roll_No must be unique.
Two students cannot have the same Roll_No.
Two students cannot have the same Roll_No.
🔗 5. Foreign Key
A foreign key connects one table to another table.
It usually refers to a primary key in another table.
It usually refers to a primary key in another table.
STUDENT
🔑 Roll_No
🔑 Roll_No
↔
COURSE
🔑 Course_ID
🔑 Course_ID
Suppose the STUDENT table stores:
Course_ID = 10
The COURSE table must contain Course_ID = 10.
If Course_ID 10 does not exist:
❌ The database can reject the student record, depending on the defined foreign-key rules.
Course_ID = 10
The COURSE table must contain Course_ID = 10.
If Course_ID 10 does not exist:
❌ The database can reject the student record, depending on the defined foreign-key rules.
🔗 Foreign Key = Link Between Tables
✅ 6. CHECK Constraint
CHECK allows us to define a condition that data must satisfy.
Think: "Is this value acceptable?"
Think: "Is this value acceptable?"
Age must be greater than or equal to 18.
✓
Age = 25
25 ≥ 18
✅ ACCEPTED
Now consider:
Age = 12
12 ≥ 18 → FALSE
❌ Record rejected.
Age = 12
12 ≥ 18 → FALSE
❌ Record rejected.
CHECK = Condition must be TRUE.
⚙️ 7. DEFAULT Constraint
DEFAULT automatically provides a value when the user
does not supply one.
| Student | Status |
|---|---|
| Rahul | Active |
| Anita | Active |
| Arjun | Active |
1
User enters a new student.
2
User does not enter Status.
3
Database automatically uses the DEFAULT value.
4
Status becomes "Active".
🛡️ 8. Referential Integrity
Referential integrity ensures that relationships between
tables remain valid.
A foreign-key value should normally refer to an existing record in the referenced table.
A foreign-key value should normally refer to an existing record in the referenced table.
COURSE
Course_ID
10
Course_ID
10
←
STUDENT
Course_ID
10
Course_ID
10
✅ Course 10 exists.
Therefore the Student record referring to Course 10 is valid.
Therefore the Student record referring to Course 10 is valid.
❌ Suppose Student contains:
Course_ID = 99
But Course 99 does not exist.
The relationship is invalid.
Course_ID = 99
But Course 99 does not exist.
The relationship is invalid.
🛡️ Referential Integrity protects the
connection between tables.
🌟 Database Constraints — Quick View
| Constraint | Simple Question | Example |
|---|---|---|
| DOMAIN | What type/value is allowed? | Age must be numeric |
| NOT NULL | Can it be empty? | Name cannot be empty |
| UNIQUE | Can it repeat? | Email cannot repeat |
| PRIMARY KEY | What uniquely identifies it? | Roll_No |
| FOREIGN KEY | How are tables linked? | Course_ID |
| CHECK | Does it satisfy the condition? | Age >= 18 |
| DEFAULT | What happens if nothing is entered? | Status = Active |
| REFERENTIAL INTEGRITY | Is the table relationship valid? | Course_ID must exist |
🎯 Easy Memory Formula
DOMAIN → What is allowed?
NOT NULL → Must it be filled?
UNIQUE → Can it repeat?
PRIMARY KEY → Who is this?
FOREIGN KEY → Which table is it connected to?
CHECK → Is the condition satisfied?
DEFAULT → What happens if nothing is entered?
REFERENTIAL INTEGRITY → Is the connection valid?
DOMAIN → What is allowed?
NOT NULL → Must it be filled?
UNIQUE → Can it repeat?
PRIMARY KEY → Who is this?
FOREIGN KEY → Which table is it connected to?
CHECK → Is the condition satisfied?
DEFAULT → What happens if nothing is entered?
REFERENTIAL INTEGRITY → Is the connection valid?
No comments:
Post a Comment