SQL Constraints and Keys

Application code has bugs, users type strange things, and scripts get run twice. Constraints are rules the database itself enforces, so bad data can’t get in no matter where it comes from. They’re your last and strongest line of defence for data quality.

The constraints#

Constraint	Rule
PRIMARY KEY	Uniquely identifies each row; never NULL
FOREIGN KEY	Value must exist in another table
UNIQUE	No two rows may have the same value
NOT NULL	A value is required
CHECK	Value must pass a condition
DEFAULT	Value used when none is given

A table with every constraint#

CREATE TABLE reviews (
  id          INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment     TEXT,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (product_id, customer_id)
);

Read it rule by rule:

• Every review has a unique id.
• It must point to a real product and a real customer.
• The rating is required and must be 1–5.
• The comment is optional.
• created_at fills itself in.
• A customer can review each product only once (the pair must be unique).
• If a product is deleted, its reviews are deleted with it.

Testing the rules#

Turn on foreign keys if you’re using SQLite, then try some inserts:

PRAGMA foreign_keys = ON;  -- SQLite only

INSERT INTO reviews (product_id, customer_id, rating, comment)
VALUES (1, 1, 5, 'Great paper quality');           -- OK

INSERT INTO reviews (product_id, customer_id, rating)
VALUES (1, 2, 7);                                  -- CHECK fails: rating must be 1–5

INSERT INTO reviews (product_id, customer_id, rating)
VALUES (1, 1, 4);                                  -- UNIQUE fails: Asha already reviewed product 1

INSERT INTO reviews (product_id, customer_id, rating)
VALUES (99, 1, 4);                                 -- FOREIGN KEY fails: no product 99

INSERT INTO reviews (product_id, customer_id)
VALUES (2, 1);                                     -- NOT NULL fails: rating is required

Each bad insert is rejected with an error explaining which rule failed. Your application can catch these errors and show a friendly message.

Primary keys#

• Exactly one per table; values unique and never NULL.
• Usually a meaningless number (id) generated by the database. Don’t use real-world values like email or phone as primary keys: people change them.
• A composite primary key uses several columns, like PRIMARY KEY (order_id, product_id) in order_items: each product appears at most once per order.

Foreign keys#

A foreign key links a column to the primary key of another table:

-- customer_id INTEGER REFERENCES customers(id)
-- or, as a separate line:
-- FOREIGN KEY (customer_id) REFERENCES customers(id)

It guarantees referential integrity: every order belongs to a customer who exists. Without it, deleting customers would leave orphaned orders pointing at nothing.

ON DELETE options#

Option	When the parent row is deleted…
RESTRICT / NO ACTION (default)	The delete is refused while children exist
CASCADE	Children are deleted too
SET NULL	Children’s foreign key becomes NULL

Choose per relationship:

• Reviews of a deleted product → CASCADE (they’re meaningless alone).
• Orders of a deleted customer → RESTRICT (keep financial records; deactivate the customer instead).
• Posts by a deleted author → SET NULL (keep the posts, show “former author”).

There’s also ON UPDATE CASCADE, rarely needed when ids never change.

UNIQUE#

-- email VARCHAR(100) UNIQUE

Guarantees no duplicates, e.g. one account per email. It’s much safer than checking in application code (“is this email taken?”) because two sign-ups at the same instant could both pass the check. The database never lets both through.

Most databases allow several NULLs in a UNIQUE column, since NULL means “unknown”. That’s why Sara (no email) doesn’t conflict with anyone.

CHECK#

-- price DECIMAL(10,2) CHECK (price >= 0)
-- status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
-- CHECK (end_date >= start_date)

Any condition on the row’s own values. (MySQL enforces CHECK from version 8.0.16; older versions ignored it.)

Adding constraints to existing tables#

-- MySQL / PostgreSQL:
-- ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price >= 0);
-- ALTER TABLE customers ADD CONSTRAINT email_unique UNIQUE (email);

Adding a constraint fails if existing data already breaks it, so clean the data first. (SQLite has limited ALTER TABLE support; there you usually recreate the table.) Naming constraints makes error messages clearer.

Relationship design#

One-to-many#

One customer → many orders. Put the foreign key on the “many” side: orders.customer_id.

Many-to-many#

One order has many products; one product appears in many orders. You need a junction table in between: order_items(order_id, product_id, qty), with two foreign keys and a composite primary key. Other examples: students ↔ courses (enrollments), posts ↔ tags (post_tags).

CREATE TABLE tags (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE product_tags (
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  tag_id     INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, tag_id)
);

One-to-one#

One user ↔ one profile. Use a foreign key that’s also UNIQUE (or the primary key itself). Often it’s simpler to keep the columns in one table.

Common mistakes#

• No foreign keys “for speed”, then years of orphaned and inconsistent data.
• Natural keys (email, phone) as primary keys.
• Duplicate checks only in code, instead of UNIQUE.
• CASCADE everywhere, so one mistaken delete wipes related history.
• **Forgetting PRAGMA foreign_keys = ON** in SQLite.

Exercise#

1. Create the reviews table and test each failing insert above.
2. Add a CHECK so product stock can never be negative (on a new test table).
3. Design tables for students, courses and enrollments (with an enrollment date), including all keys.
4. Decide the ON DELETE behaviour for: comments when a post is deleted; invoices when a customer is deleted. Explain why.

Summary#

• Constraints make the database enforce data rules: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
• Foreign keys keep relationships valid; ON DELETE decides what happens to children.
• UNIQUE is the reliable way to prevent duplicates.
• One-to-many uses a foreign key; many-to-many uses a junction table.

Next, you’ll make queries fast with indexes.