Constraints and Data Integrity

Constraints are rules attached to a column or table that the database enforces on every insert and update, rejecting anything that violates them. You've already seen two — PRIMARY KEY and FOREIGN KEY, from a couple of lessons back — but several more are worth knowing.

NOT NULL

Requires a column to always have a value:

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

Without it, email would silently accept NULL — useful to catch at the database level, since a missing required field is a data problem worth rejecting immediately rather than discovering later when something downstream assumes it's always present.

UNIQUE

Guarantees no two rows share the same value in that column, without making it the primary key:

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

A table can have exactly one primary key, but any number of UNIQUE columns — email here is a good candidate, since two users should never share one, even though id remains the actual identifier used in foreign key relationships elsewhere.

CHECK

Enforces an arbitrary boolean condition on a column's value:

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price >= 0)
);

Any insert or update that would leave price negative is rejected outright. CHECK support and exact syntax varies more between dialects than the other constraints here — older MySQL versions historically parsed but silently ignored CHECK, a good example of why testing a constraint actually works on your specific database matters, not just writing it.

DEFAULT

Not strictly a constraint on validity, but closely related — supplies a value automatically when a column is omitted from an INSERT:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Why enforce this in the database, not just application code

It's tempting to validate everything in application code and treat the database as a passive store. The problem is that a database is very often written to from more than one place over its lifetime — a background job, an admin script, a second service, a manual fix during an incident — and application-level validation only runs from within the application that wrote it. A constraint declared on the table itself is enforced no matter what writes the row, which is the only way to guarantee the rule actually holds for every row in the table, not just the ones inserted through the code path you remembered to validate.

With schema design covered, the next lessons turn to performance and real-world use — starting with indexes, which speed up exactly the kind of filtering and joining this course has covered so far.