Constraints
## Learning Objectives
- Understand database constraints
- Use PRIMARY KEY, FOREIGN KEY
- Implement NOT NULL, UNIQUE
- Add CHECK constraints
## What are Constraints?
Rules enforced on table data:
```sql
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
salary DECIMAL CHECK (salary > 0)
);
```
## PRIMARY KEY
### Single Column
```sql
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT
);
```
### Composite Primary Key
```sql
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
```
### Add to Existing Table
```sql
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);
```
### AUTO_INCREMENT
```sql
-- MySQL
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTO_INCREMENT
);
-- PostgreSQL (SERIAL)
CREATE TABLE employees (
id SERIAL PRIMARY KEY
);
-- SQLite
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT
);
-- SQL Server
CREATE TABLE employees (
id INTEGER IDENTITY(1,1) PRIMARY KEY
);
```
## FOREIGN KEY
### Basic Syntax
```sql
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name TEXT,
department_id INTEGER REFERENCES departments(id)
);
```
### With ON DELETE/UPDATE
```sql
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
department_id INTEGER REFERENCES departments(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
```
### Options
| Option | Description |
|--------|-------------|
| CASCADE | Delete/update row, related rows also deleted/updated |
| SET NULL | Set foreign key to NULL |
| SET DEFAULT | Set foreign key to default value |
| RESTRICT | Prevent delete/update if related rows exist |
| NO ACTION | Same as RESTRICT (check after other operations) |
### Composite Foreign Key
```sql
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
FOREIGN KEY (order_id, product_id) REFERENCES orders(id, product_id)
);
```
## NOT NULL
### Column Level
```sql
CREATE TABLE employees (
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL
);
```
### Prevent NULL in Existing
```sql
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;
```
## UNIQUE
### UNIQUE Single Column
```sql
CREATE TABLE employees (
email TEXT UNIQUE
);
```
### Multiple Columns (Row Level)
```sql
CREATE TABLE department_leads (
department_id INTEGER,
leader_id INTEGER,
UNIQUE (department_id, leader_id)
);
```
### Named Constraint
```sql
CREATE TABLE employees (
email TEXT CONSTRAINT uq_email UNIQUE
);
```
## CHECK Constraint
### Basic CHECK
```sql
CREATE TABLE employees (
salary DECIMAL CHECK (salary > 0),
age INTEGER CHECK (age >= 18)
);
```
### Named CHECK
```sql
CREATE TABLE products (
price DECIMAL CONSTRAINT positive_price CHECK (price >= 0),
quantity INTEGER CONSTRAINT positive_qty CHECK (quantity >= 0)
);
```
### Multiple Conditions
```sql
CREATE TABLE reservations (
check_in DATE,
check_out DATE,
CHECK (check_out > check_in)
);
```
### Add CHECK to Existing
```sql
ALTER TABLE employees
ADD CONSTRAINT positive_salary CHECK (salary > 0);
```
## DEFAULT
### Column Default
```sql
CREATE TABLE employees (
created_at DATE DEFAULT CURRENT_DATE,
status TEXT DEFAULT 'active',
is_active BOOLEAN DEFAULT TRUE
);
```
### Sequence as Default
```sql
-- PostgreSQL
CREATE TABLE employees (
id SERIAL PRIMARY KEY DEFAULT nextval('my_sequence')
);
```
### Expression as Default
```sql
-- PostgreSQL
CREATE TABLE orders (
total DECIMAL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
## Constraint Management
### View Constraints
```sql
-- MySQL
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
-- PostgreSQL
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
```
### Drop Constraint
```sql
-- MySQL
ALTER TABLE employees DROP INDEX uq_email;
-- PostgreSQL / SQL Server
ALTER TABLE employees DROP CONSTRAINT uq_email;
```
## Naming Conventions
```sql
-- Consistent naming
PRIMARY KEY: pk_tablename
FOREIGN KEY: fk_tablename_columnname
UNIQUE: uq_tablename_columnname
CHECK: chk_tablename_condition
```
## Common Patterns
### User Table
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
```
### Audit Table
```sql
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
action TEXT NOT NULL,
table_name TEXT NOT NULL,
record_id INTEGER NOT NULL,
old_value TEXT,
new_value TEXT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
changed_by INTEGER REFERENCES users(id)
);
```
## Summary
- **PRIMARY KEY**: Uniquely identifies each row (one per table)
- **FOREIGN KEY**: Links to another table (enforces referential integrity)
- **NOT NULL**: Prevents NULL values
- **UNIQUE**: No duplicate values allowed
- **CHECK**: Custom validation rules
- **DEFAULT**: Value when none provided
- Constraints maintain data integrity at database level
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →