AI Hints
Home›SQL Tutorial›Rules, Safety, and Speed
SQL · Lesson 74 of 126

Constraints

Rules that protect your data.

⏱ 2 min read▶ Video at 1:44:04🎓 Free
Watch this lesson in the SQL courseWatch on YouTube ↗Full SQL course

A database is only as good as the data inside it. Duplicate emails, negative ages, orders for customers that don't exist. Bad data causes wrong answers later. Constraints are rules you attach to columns, and the database refuses any row that breaks them.

Constraints
From the video: Constraints
  • Bad data, wrong answers
  • Rules on columns
  • The database says no

UNIQUE

Helen is starting a members' club. Each email must be used only once, so the email column is unique, and not null. The first insert works. The second uses the same email. Run it. The database refuses, with a unique constraint error. That's a good thing. The bad row never got in.

query.sqlsql
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    email     TEXT UNIQUE NOT NULL,
    age       INTEGER CHECK (age >= 16)
);
INSERT INTO members (email, age) VALUES ('ana@mail.com', 34);
INSERT INTO members (email, age) VALUES ('ana@mail.com', 29);
Output
Runtime error near line 7: UNIQUE constraint failed: members.email (19)

CHECK

Check lets you write your own rule. Members must be sixteen or older. Let's try a twelve year old. Run it. Check constraint failed. Again, the database protects you.

query.sqlsql
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    email     TEXT UNIQUE NOT NULL,
    age       INTEGER CHECK (age >= 16)
);
INSERT INTO members (email, age) VALUES ('leo@mail.com', 12);
Output
Runtime error near line 6: CHECK constraint failed: age >= 16 (19)

FOREIGN KEY

A foreign key makes sure a row points to something real. In SQLite, you switch this checking on with a pragma. Order items point to orders. So let's try to delete an order that still has items. Run it. The database stops us. No orphaned order lines.

query.sqlsql
PRAGMA foreign_keys = ON;

DELETE FROM orders
WHERE order_id = 1001;
Output
Runtime error near line 3: FOREIGN KEY constraint failed (19)

The constraints you'll meet

Here are the five you'll meet most often. Primary key, unique, and not null for every row. Check for your own rules. And foreign key to link tables safely.

PRIMARY KEYunique ID per row
NOT NULLvalue required
UNIQUEno duplicates
CHECKyour own rule
FOREIGN KEYmust point to a real row

Summary

Constraints stop bad data at the door. Next, how to make several changes safely, all or nothing.

Key takeaways
  • UNIQUE, NOT NULL
  • CHECK (rule)
  • FOREIGN KEY
Next up: Transactions — all or nothing