Database Constraints & Normalization
SQL Basics Course
Topic 14: Database Constraints & Normalization
Learn how databases enforce data integrity and eliminate data redundancy.
What Are Constraints?
Constraints are rules applied to table columns to ensure valid and reliable data.
- Improve data quality
- Prevent invalid entries
- Maintain consistency
- Enforce business rules
PRIMARY KEY
A PRIMARY KEY uniquely identifies every row in a table.
id INT PRIMARY KEY,
name VARCHAR(100)
);
- Cannot contain NULL values
- Must be unique
- One primary key per table
FOREIGN KEY
A FOREIGN KEY creates relationships between tables.
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
UNIQUE Constraint
Ensures no duplicate values exist.
NOT NULL Constraint
Forces users to provide a value.
DEFAULT Constraint
Assigns a default value if none is supplied.
CHECK Constraint
Validates data before insertion.
Combining Multiple Constraints
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0),
status VARCHAR(20) DEFAULT 'Active'
);
What is Normalization?
Normalization is the process of organizing data to reduce redundancy and improve consistency.
Problem Without Normalization
| Student | Course | Teacher |
|---|---|---|
| Rahul | SQL | John |
| Rahul | Laravel | Sarah |
Student information is repeated multiple times.
First Normal Form (1NF)
Every column should contain atomic values.
| Bad | Good |
|---|---|
| SQL, Laravel | SQL |
| Laravel |
Second Normal Form (2NF)
- Must satisfy 1NF
- Remove partial dependencies
- Split data into related tables
Third Normal Form (3NF)
- Must satisfy 2NF
- Remove transitive dependencies
- Store facts only once
Normalization Flow
↓
1NF
↓
2NF
↓
3NF
Real Business Example
An e-commerce system should use separate tables:
- customers
- products
- orders
- order_items
Instead of storing everything in one giant table.
Best Practices
- Always use Primary Keys
- Use Foreign Keys for relationships
- Normalize up to 3NF
- Avoid duplicate data
- Apply constraints wherever possible
Practice Exercises
- Create a table using a PRIMARY KEY.
- Create a FOREIGN KEY relationship.
- Create a UNIQUE email field.
- Normalize a student-course table.
- Create a table using all major constraints.
Quiz
1. What is a PRIMARY KEY?
2. What is a FOREIGN KEY?
3. What does UNIQUE do?
4. What is 1NF?
5. Why is normalization important?
Summary
- Constraints protect data integrity.
- PRIMARY KEY uniquely identifies rows.
- FOREIGN KEY creates table relationships.
- UNIQUE prevents duplicate values.
- NOT NULL requires data.
- DEFAULT provides fallback values.
- CHECK validates inputs.
- Normalization reduces redundancy and improves design.