Constraints: Rules the Database Enforces

Teaching the table to say no

In lesson 1-2 we promised that a database can refuse bad data. Constraints are those rules, declared right in CREATE TABLE:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 13),
  plan TEXT DEFAULT 'free'
);

• PRIMARY KEY: this column uniquely identifies each row. In SQLite an INTEGER PRIMARY KEY also auto-numbers itself when you do not supply a value.
• NOT NULL: the value may never be missing.
• UNIQUE: no two rows may share a value. Perfect for emails and usernames.
• CHECK (...): an arbitrary test every row must pass.
• DEFAULT ...: the value used when an INSERT does not provide one.

A constraint violation makes the INSERT or UPDATE fail with an error instead of storing bad data. The mistake is stopped at the door.

PRIMARY KEY and DEFAULT filling themselves in

Neither INSERT mentions id, yet both rows get one. Ana never chose a plan, so the DEFAULT supplied free.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 13),
  plan TEXT DEFAULT 'free'
);

INSERT INTO users (email, age) VALUES ('ana@example.com', 25);
INSERT INTO users (email, age, plan) VALUES ('ben@example.com', 17, 'premium');

SELECT * FROM users;

Output

1|ana@example.com|25|free
2|ben@example.com|17|premium

An INTEGER PRIMARY KEY in SQLite assigns itself, counting up from 1, which is why the column list can skip it. Other databases spell the same idea differently, with SERIAL in PostgreSQL or AUTO_INCREMENT in MySQL.

Four constraints are declared on this table and none of them has done anything visible yet, because both rows are valid. The next example is where they start refusing work.

Constraints refusing bad rows

This block is expected to produce errors, and the errors are the interesting part. The duplicate email violates UNIQUE, and the nine-year-old violates the CHECK. The final SELECT still runs and shows that only Ana made it in.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 13)
);

INSERT INTO users (email, age) VALUES ('ana@example.com', 25);

-- Duplicate email: UNIQUE says no
INSERT INTO users (email, age) VALUES ('ana@example.com', 30);

-- Age 9: CHECK (age >= 13) says no
INSERT INTO users (email, age) VALUES ('kid@example.com', 9);

SELECT * FROM users;

The rejected statements fail individually and leave the table untouched, so a bad row is never half-written. Each failed INSERT is an all-or-nothing event.

The point of enforcing this in the database rather than in application code is that the rule holds no matter who writes the row. A second service, a migration script, or somebody typing at a console all hit the same wall.

The constraint between tables: FOREIGN KEY

Unit 5's foreign keys have been an honor system so far: nothing stopped an orders row from pointing at customer 9 when no customer 9 exists. Such a row is called an orphan, a pointer to nothing, and it silently breaks every join that touches it. Declaring the relationship turns the honor system into an enforced rule:

customer_id INTEGER NOT NULL REFERENCES customers(id)

REFERENCES customers(id) means every value stored in this column must exist in customers.id. An INSERT that points nowhere is rejected, and deleting a customer who still has orders is rejected too, so the two tables can never fall out of agreement.

One SQLite quirk: for historical reasons this checking is off by default, so scripts switch it on first with PRAGMA foreign_keys = ON;. Server databases like PostgreSQL and MySQL enforce it always.

A FOREIGN KEY rejecting an orphan

The first order points at a real customer and is stored. The second points at customer 9, who does not exist, so the foreign key constraint rejects it, and the final SELECT proves only the valid order survived. This block is expected to error.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT
);

INSERT INTO customers VALUES (1, 'Ana');

INSERT INTO orders VALUES (101, 1, 'coffee');
INSERT INTO orders VALUES (102, 9, 'bagel');

SELECT * FROM orders;

REFERENCES customers(id) turns the informal pointer from unit 5 into an enforced rule: a customer_id must name a customer who actually exists. An order belonging to nobody, often called an orphan row, becomes impossible rather than merely unlikely.

The PRAGMA foreign_keys = ON line is a SQLite quirk worth remembering, since SQLite ignores foreign keys unless they are switched on for the connection. PostgreSQL and MySQL enforce them by default.

The rule works in the other direction too. Deleting Ana while order 101 still references her is refused, unless the table declares what should happen instead, such as ON DELETE CASCADE.

Choosing the constraint for a missing value

When signups sometimes arrive with no email and buggy application code inserts them anyway, NOT NULL on the email column is what stops the bad rows at the database.

NOT NULL rejects any row that has no email, loudly and at the moment of the write, which is exactly the behavior you want from a bug you have not found yet.

The alternatives all fail in instructive ways:

NOT NULL and UNIQUE are frequently declared together on an email column, since each catches a different mistake. The primary key is normally a separate numeric id, which keeps the key stable when a user changes their address.

Declaring a products table

Constraints go directly after the column type, following the users table from earlier in this lesson as a template.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER CHECK (price > 0),
  stock INTEGER DEFAULT 0
);

INSERT INTO products (name, price) VALUES ('Notebook', 4);
INSERT INTO products (name, price, stock) VALUES ('Pen', 2, 50);

SELECT * FROM products;

Output

1|Notebook|4|0
2|Pen|2|50

Both ids assign themselves, and the Notebook row provides no stock, so DEFAULT 0 fills it in. A default of 0 is a much better description of a new product than NULL, which would mean the stock level is unknown rather than empty.

CHECK (price > 0) rules out free and negative prices, which is the kind of rule that pays for itself the first time a form submits an empty field as a zero.