A table without rules will accept anything: two customers with the same ID, an order for a customer who does not exist, a product with a price of minus ₹50. SQL constraints are rules you attach to columns in CREATE TABLE. The database checks them on every insert and update and refuses changes that break them. That protection works no matter which app, script or person touches the data.
The six common constraints
| Constraint | Rule it enforces | Example |
|---|---|---|
PRIMARY KEY | Uniquely identifies each row; cannot repeat | CustomerID INTEGER PRIMARY KEY |
FOREIGN KEY | Value must exist in another table | CustomerID INTEGER REFERENCES Customers(CustomerID) |
NOT NULL | A value is required | CustomerName TEXT NOT NULL |
UNIQUE | No two rows may share the value | Email TEXT UNIQUE |
CHECK | Value must pass a condition | CHECK (Price > 0) |
DEFAULT | Value used when none is given | Status TEXT DEFAULT 'Pending' |
Strictly speaking, DEFAULT does not reject anything, but it is defined in the same place and usually taught alongside the others.
NOT NULL, UNIQUE, CHECK and DEFAULT
The example below creates a table for a loyalty programme. Every member must have a name, each email can be registered only once, the age must be 18 or more, and new members start with 0 points.
CREATE TABLE Members (
MemberID INTEGER PRIMARY KEY,
FullName TEXT NOT NULL,
Email TEXT NOT NULL UNIQUE,
Age INTEGER CHECK (Age >= 18),
Points INTEGER NOT NULL DEFAULT 0
);
INSERT INTO Members (FullName, Email, Age)
VALUES ('Lakshmi R', '[email protected]', 34),
('Arun K', '[email protected]', 27);
SELECT * FROM Members;Returns 2 rows, each with Points 0 from the DEFAULT.
Now break the rules on purpose. Change Arun’s email to [email protected] and run again: SQLite stops with UNIQUE constraint failed: Members.Email. Change his age to 16 and you get CHECK constraint failed. Replace his name with NULL and you get NOT NULL constraint failed. Reading these messages is a skill in itself – they tell you exactly which rule was broken.
Tip: MySQL has enforced CHECK constraints only since version 8.0.16; older versions accepted the syntax but silently ignored it. If you work on an older server, validate such rules in your PHP code as well.
Primary keys
A primary key is the column (or set of columns) that identifies a row. Every table should have one. It is automatically unique and, in standard SQL, cannot be NULL. (SQLite keeps an old quirk that allows NULL in a non-integer primary key, so add NOT NULL there to be safe – as in the next example.) Most tables use a meaningless auto-numbered ID, called a surrogate key, because real-world values such as phone numbers or email addresses can change.
Sometimes a pair of columns identifies a row. A warehouse stock table has one row per product per city, so the key is the combination. This is a composite primary key, written as a separate line at the end of the column list:
CREATE TABLE Stock (
WarehouseCity TEXT NOT NULL,
ProductID INTEGER NOT NULL,
Qty INTEGER NOT NULL DEFAULT 0 CHECK (Qty >= 0),
PRIMARY KEY (WarehouseCity, ProductID)
);
INSERT INTO Stock VALUES
('Chennai', 1, 120),
('Chennai', 2, 80),
('Puducherry', 1, 45);
SELECT * FROM Stock ORDER BY WarehouseCity, ProductID;Returns 3 rows. Product 1 appears twice, but each city-product pair appears only once.
Try adding ('Chennai', 1, 10) to the insert: the pair already exists, so it is rejected. ('Madurai', 1, 10) is fine.
Foreign keys
A foreign key links a column to the primary key of another table and guarantees the link is valid. In the sample database, Orders.CustomerID references Customers(CustomerID), so an order cannot point to a customer who does not exist. SQLite can list the foreign keys of a table:
PRAGMA foreign_key_list('OrderDetails');Returns 2 rows: ProductID references Products(ProductID) and OrderID references Orders(OrderID).
Watch out: SQLite does not enforce foreign keys unless you switch them on with PRAGMA foreign_keys = ON; – and you must do it for every new connection. Without it, the REFERENCES clauses are stored but ignored. MySQL’s default InnoDB engine enforces foreign keys automatically, but the older MyISAM engine ignores them.
What happens when a parent row is deleted?
When a customer is deleted, what should happen to their orders? You decide with an ON DELETE action:
RESTRICT/NO ACTION(the default) – refuse to delete the parent while children exist.CASCADE– delete the child rows too.SET NULL– keep the child rows but empty their foreign key column.
PRAGMA foreign_keys = ON;
CREATE TABLE Branches (
BranchID INTEGER PRIMARY KEY,
City TEXT NOT NULL
);
CREATE TABLE Staff (
StaffID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
BranchID INTEGER NOT NULL
REFERENCES Branches(BranchID) ON DELETE CASCADE
);
INSERT INTO Branches VALUES (1, 'Puducherry'), (2, 'Madurai');
INSERT INTO Staff (Name, BranchID) VALUES
('Meena', 1), ('Senthil', 2), ('Kavya', 2);
DELETE FROM Branches WHERE BranchID = 2;
SELECT * FROM Staff;Returns 1 row: Meena. Closing the Madurai branch also removed Senthil and Kavya.
Change CASCADE to RESTRICT and run again: the delete now fails with FOREIGN KEY constraint failed. Or add INSERT INTO Staff (Name, BranchID) VALUES ('Ravi', 9); – there is no branch 9, so it is refused. CASCADE is convenient, but use it only where losing the child rows is truly what you want; for orders and invoices, RESTRICT is usually safer.
SQL constraints in a real application
Constraints and app-side validation work together. Your form should check input and show friendly messages, while SQL constraints are the final safety net. In PHP with PDO, a broken constraint throws a PDOException with SQLSTATE 23000 (integrity constraint violation), which you can catch to show “This email is already registered”. See how in PHP MySQL with PDO.
This is the last lesson of the SQL track. You can now read, filter, sort, summarise, join and change data, and design tables that protect it. Go back to the SQL track page to revise any topic, or start again with SQL Introduction.