Lesson 4: SQL Constraints (Rules for Your Data).
Welcome to Lesson 4. In the last lesson, we learned about Data Types (Text, Numbers, Dates). But simply knowing the type isn't enough.
Imagine a "Sign Up" form on a website. What happens if:
- A user tries to sign up without a password?
- Two users try to use the same email address?
The database must stop this! We use Constraints to enforce these rules.
1. What are SQL Constraints?
Constraints are rules we apply to columns to ensure the data is accurate and reliable. If you try to insert data that breaks a rule, the database will say "Error" and reject it.
2. The 6 Most Important Constraints
Here are the rules you will use in almost every table:
| Constraint | The Rule | Real Life Example |
|---|---|---|
| NOT NULL | The column cannot be empty. | A user must have a Password. |
| UNIQUE | No duplicate values allowed. | Two users cannot have the same Email. |
| DEFAULT | Sets a default value if none is provided. | If user doesn't select a country, default to 'India'. |
| CHECK | Ensures values satisfy a specific condition. | Age must be greater than 18. |
| PRIMARY KEY | Uniquely identifies each row (ID Card). | Employee ID, Student Roll No. |
| FOREIGN KEY | Links this table to another table. | Links an Order to a specific Customer. |
3. Understanding Primary vs Foreign Key
This is the concept that makes a database "Relational."
- Primary Key (PK): My ID card. It identifies ME.
- Foreign Key (FK): A reference to YOUR ID card. It connects me to you.
Interview Question: What is the difference?
| Feature | Primary Key (PK) | Foreign Key (FK) |
|---|---|---|
| Uniqueness | Must be Unique. | Can have Duplicates. |
| Nulls | Cannot be NULL (Empty). | Can be NULL. |
| Limit | Only 1 per table. | Multiple allowed per table. |
4. Syntax: How to Apply Rules
We apply these rules when we CREATE the table. Look at this example:
student_id INT PRIMARY KEY,
email VARCHAR(50) UNIQUE,
city VARCHAR(20) DEFAULT 'Bangalore',
age INT CHECK (age >= 18)
);
5. Your Turn: Test the Rules!
We are going to try to "break" the database to see the constraints in action.
Step 1: Open OracleSQL or SQL Fiddle.
Step 2: Copy this code. We will create two tables to test the Foreign Key:
CREATE TABLE dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(20)
);
-- 2. Create the Child Table (Employees)
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
city VARCHAR(20) DEFAULT 'Mumbai',
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
-- 3. Insert valid data
INSERT INTO dept VALUES (101, 'HR');
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'Raju', 101);
Step 3: Now, let's try to break the rules!
INSERT INTO employees VALUES (2, 'Wrong Dept', 'Delhi', 999);
-- Error 2: Check Default Value (We skip the city)
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Priya', 101);
-- Check the results
SELECT * FROM employees;
Result:
1. The database rejects User 2 because Dept 999 doesn't exist.
2. User 3 (Priya) is automatically assigned to 'Mumbai' because of the DEFAULT rule.
Summary: Today we learned that Constraints are the "Bodyguards" of the database. They protect us from bad data using rules like NOT NULL, UNIQUE, and FOREIGN KEYS.
Next Lesson: In Lesson 5, we will learn how to change data using UPDATE and DELETE (and how to avoid deleting everything by mistake!).
Comments
Post a Comment