Chapter 6: Constraints
Constraints are the rules you place on your columns to make sure the data inside your tables is correct, consistent, and meaningful. Think of them as the “guardrails” that prevent bad data from entering your database — like making sure no one enters a negative age, or duplicate email addresses, or orders linked to non-existent customers.
Today we’re going to learn all the major constraints you’ll use every day:
- NOT NULL → This field cannot be empty
- UNIQUE → No two rows can have the same value in this column
- PRIMARY KEY → Unique + Not Null + identifies each row
- FOREIGN KEY → Links this column to another table’s primary key
- CHECK → Custom rule (e.g., age > 18)
- DEFAULT → Automatically fill a value if none is provided
- AUTO_INCREMENT / IDENTITY → Automatically generate unique numbers (usually for IDs)
We’ll explain each one in detail with real examples, common mistakes, best practices, and full runnable code. We’ll continue using our bookshop database as the example.
Let’s start!
1. NOT NULL – The Field Cannot Be Empty
This is the simplest and most common constraint. It means: You must provide a value when inserting or updating this column — no NULL allowed.
Syntax:
|
0 1 2 3 4 5 6 |
column_name datatype NOT NULL |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, -- Title must always be provided author VARCHAR(150) NOT NULL, price DECIMAL(10,2) NOT NULL ); |
What happens if you try to insert without title?
|
0 1 2 3 4 5 6 7 8 |
INSERT INTO books (book_id, author, price) VALUES (1, 'Ruskin Bond', 299.99); -- ERROR: Column 'title' cannot be null |
Best Practice: Use NOT NULL for almost every important column: names, emails, prices, dates, etc. Only allow NULL when it truly makes sense (e.g., middle_name might be optional).
2. UNIQUE – No Duplicates Allowed
Ensures that all values in this column are different — no two rows can have the same value.
Syntax:
|
0 1 2 3 4 5 6 |
column_name datatype UNIQUE |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE customers ( customer_id INT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, -- No two customers can have same email phone VARCHAR(15) UNIQUE -- Optional: unique phone too ); |
What happens if you try duplicate email?
|
0 1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO customers (customer_id, email, phone) VALUES (1, 'raj@example.com', '9876543210'); INSERT INTO customers (customer_id, email, phone) VALUES (2, 'raj@example.com', '9123456789'); -- ERROR: Duplicate entry 'raj@example.com' for key 'email' |
Important: UNIQUE allows one NULL value (in most databases), but NOT NULL + UNIQUE is the common combo for emails, usernames, etc.
3. PRIMARY KEY – The Unique Identifier for Each Row
A primary key is a column (or combination of columns) that:
- Is UNIQUE
- Is NOT NULL
- Uniquely identifies every row in the table
Almost every table should have one!
Syntax:
|
0 1 2 3 4 5 6 7 8 |
column_name datatype PRIMARY KEY -- or for composite (multiple columns): PRIMARY KEY (column1, column2) |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Most common pattern title VARCHAR(255) NOT NULL, isbn CHAR(13) UNIQUE NOT NULL ); |
Alternative (composite primary key – rare but useful):
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE order_items ( order_id INT, book_id INT, quantity INT, PRIMARY KEY (order_id, book_id) -- Combination of order + book must be unique ); |
Best Practice: Use a single-column surrogate key (like id INT AUTO_INCREMENT PRIMARY KEY) — simple, fast, and reliable.
4. FOREIGN KEY – Linking Tables Together
A foreign key is a column that references the primary key of another table. It enforces referential integrity — you can’t have an order for a customer who doesn’t exist!
Syntax:
|
0 1 2 3 4 5 6 |
FOREIGN KEY (column_name) REFERENCES parent_table(parent_column) |
Real example – Two tables linked:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- First, the parent table CREATE TABLE authors ( author_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL ); -- Then the child table with foreign key CREATE TABLE books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author_id INT UNSIGNED NOT NULL, FOREIGN KEY (author_id) REFERENCES authors(author_id) ); |
What happens if you try to insert a book with non-existent author?
|
0 1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO authors (name) VALUES ('Arundhati Roy'); INSERT INTO books (title, author_id) VALUES ('The God of Small Things', 1); -- Works INSERT INTO books (title, author_id) VALUES ('Unknown Book', 999); -- ERROR! -- Cannot add or update a child row: a foreign key constraint fails |
Bonus options (very useful):
|
0 1 2 3 4 5 6 7 8 |
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE -- If author is deleted, delete all their books ON UPDATE CASCADE; -- If author_id changes, update all books |
5. CHECK – Custom Rules for Values
Lets you define your own rule — e.g., price must be positive, age > 18, etc.
Syntax:
|
0 1 2 3 4 5 6 |
CHECK (condition) |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, salary DECIMAL(10,2) NOT NULL, age INT NOT NULL, CHECK (salary >= 10000), -- Minimum salary CHECK (age >= 18 AND age <= 65) -- Working age ); |
Modern syntax (column-level or table-level):
|
0 1 2 3 4 5 6 7 |
salary DECIMAL(10,2) CHECK (salary > 0), CHECK (join_date <= CURRENT_DATE) -- Can't join in future |
6. DEFAULT – Automatically Fill a Value
If no value is provided during INSERT, use this default value.
Syntax:
|
0 1 2 3 4 5 6 |
column_name datatype DEFAULT value |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE DEFAULT CURRENT_DATE, -- Today's date if not given status VARCHAR(20) DEFAULT 'PENDING', is_paid BOOLEAN DEFAULT FALSE ); |
Insert without specifying:
|
0 1 2 3 4 5 6 7 |
INSERT INTO orders (order_id) VALUES (1); -- order_date = today's date, status = 'PENDING', is_paid = FALSE |
7. AUTO_INCREMENT / IDENTITY – Automatic Unique Numbers
Automatically generates the next number for IDs — perfect for primary keys.
MySQL / MariaDB / SQLite:
|
0 1 2 3 4 5 6 |
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY |
SQL Server:
|
0 1 2 3 4 5 6 |
id INT IDENTITY(1,1) PRIMARY KEY -- Starts at 1, increments by 1 |
PostgreSQL:
|
0 1 2 3 4 5 6 |
id SERIAL PRIMARY KEY -- Or BIGSERIAL for bigger numbers |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE customers ( customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ); INSERT INTO customers (name) VALUES ('Priya Sharma'); INSERT INTO customers (name) VALUES ('Amit Patel'); -- customer_id becomes 1, 2 automatically |
Important: AUTO_INCREMENT resets only with TRUNCATE TABLE — not with DELETE.
Full Realistic Example – All Constraints Together
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE DATABASE IF NOT EXISTS bookshop; USE bookshop; -- Authors table CREATE TABLE authors ( author_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, country VARCHAR(50) DEFAULT 'India', birth_year SMALLINT CHECK (birth_year BETWEEN 1900 AND 2025) ); -- Books table with multiple constraints CREATE TABLE books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, isbn CHAR(13) UNIQUE NOT NULL, author_id INT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price >= 0), stock SMALLINT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_available BOOLEAN DEFAULT TRUE, FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE RESTRICT -- Don't allow deleting author if books exist ON UPDATE CASCADE ); -- Test it! INSERT INTO authors (name, country, birth_year) VALUES ('Chetan Bhagat', 'India', 1974); INSERT INTO books (title, isbn, author_id, price, stock) VALUES ('2 States', '9788129115300', 1, 299.00, 50); |
Common Mistakes & How to Avoid Them
| Mistake | Consequence | Fix / Best Practice |
|---|---|---|
| Forgetting NOT NULL on important columns | Lots of NULLs → bad data | Always think: “Must this always have a value?” |
| Using FLOAT for money | Rounding errors (₹9.99 → 9.989999) | Use DECIMAL for money |
| No FOREIGN KEY → orphan records | Orders for deleted customers | Always add FK when tables are related |
| Forgetting to add index on FK columns | Slow queries | MySQL auto-indexes FK, but check performance |
| Using AUTO_INCREMENT without UNSIGNED |
