What are Data Integrity Constraints?
All Constraints Summary
| Constraint | What It Does | Prevents |
|---|---|---|
| PRIMARY KEY | Unique row identifier | Duplicate IDs |
| FOREIGN KEY | Links tables | Orphan records |
| UNIQUE | No duplicate values | Repeated entries |
| NOT NULL | Required field | Empty values |
| CHECK | Custom validation | Invalid data |
5 rows
Constraints are rules that protect your data from mistakes. They automatically check and reject bad data before it enters your database.
Think of it like: A security guard checking ID at the door - only valid data gets in.
The Five Constraints
All Constraints Summary
| Constraint | What It Does | Prevents |
|---|---|---|
| PRIMARY KEY | Unique row identifier | Duplicate IDs |
| FOREIGN KEY | Links tables | Orphan records |
| UNIQUE | No duplicate values | Repeated entries |
| NOT NULL | Required field | Empty values |
| CHECK | Custom validation | Invalid data |
5 rows
1. PRIMARY KEY - Unique Identifier
Every row needs a unique ID. Cannot be empty, cannot repeat.
CREATE TABLE students (
student_id INT PRIMARY KEY, -- Each student has unique ID
name VARCHAR(100)
);
-- Works
INSERT INTO students VALUES (1, 'John');
INSERT INTO students VALUES (2, 'Jane');
-- Fails: duplicate ID
INSERT INTO students VALUES (1, 'Bob'); -- Error!2. FOREIGN KEY - Links Tables
Connects data between tables. Ensures referenced data exists.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Works: customer 5 exists
INSERT INTO orders VALUES (1, 5);
-- Fails: customer 999 does not exist
INSERT INTO orders VALUES (2, 999); -- Error!3. UNIQUE - No Duplicates
Column values must be different for each row.
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE -- No two users can share email
);
-- Works
INSERT INTO users VALUES (1, 'john@email.com');
-- Fails: email already exists
INSERT INTO users VALUES (2, 'john@email.com'); -- Error!4. NOT NULL - Required Field
Column cannot be empty. Must have a value.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL, -- Name is required
nickname VARCHAR(50) -- Nickname is optional
);
-- Works
INSERT INTO employees VALUES (1, 'John Smith', NULL);
-- Fails: name cannot be empty
INSERT INTO employees VALUES (2, NULL, 'JD'); -- Error!5. CHECK - Custom Rules
Define your own validation rules.
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0), -- Must be positive
stock INT CHECK (stock >= 0), -- Cannot be negative
discount INT CHECK (discount BETWEEN 0 AND 100) -- 0-100 range
);
-- Works
INSERT INTO products VALUES (1, 'Phone', 599.99, 50, 10);
-- Fails: negative price
INSERT INTO products VALUES (2, 'TV', -100, 10, 5); -- Error!Complete Example
All Constraints Summary
| Constraint | What It Does | Prevents |
|---|---|---|
| PRIMARY KEY | Unique row identifier | Duplicate IDs |
| FOREIGN KEY | Links tables | Orphan records |
| UNIQUE | No duplicate values | Repeated entries |
| NOT NULL | Required field | Empty values |
| CHECK | Custom validation | Invalid data |
5 rows
CREATE TABLE customers (
customer_id INT PRIMARY KEY, -- Unique ID
email VARCHAR(100) UNIQUE NOT NULL, -- Required, no duplicates
name VARCHAR(100) NOT NULL, -- Required
age INT CHECK (age >= 18), -- Must be 18+
balance DECIMAL(10,2) CHECK (balance >= 0) -- No negative balance
);This table ensures:
- Every customer has unique ID
- Email is required and unique
- Name is required
- Age must be 18 or older
- Balance cannot go negative
What Happens When Constraint Fails?
All Constraints Summary
| Constraint | What It Does | Prevents |
|---|---|---|
| PRIMARY KEY | Unique row identifier | Duplicate IDs |
| FOREIGN KEY | Links tables | Orphan records |
| UNIQUE | No duplicate values | Repeated entries |
| NOT NULL | Required field | Empty values |
| CHECK | Custom validation | Invalid data |
5 rows
The database rejects the entire operation. Your data stays clean.
Summary
All Constraints Summary
| Constraint | What It Does | Prevents |
|---|---|---|
| PRIMARY KEY | Unique row identifier | Duplicate IDs |
| FOREIGN KEY | Links tables | Orphan records |
| UNIQUE | No duplicate values | Repeated entries |
| NOT NULL | Required field | Empty values |
| CHECK | Custom validation | Invalid data |
5 rows
Constraints are your database's first line of defense against bad data.