Constraints That Protect Your Data

Make invalid data impossible to insert — NOT NULL, UNIQUE, CHECK, DEFAULT

You find a NULL in the abv column and a rating of 0 in beer_ratings. Both are logically impossible — ABV must be a positive number, and scores run from 1 to 10. The data got in because the table had no constraints.\n\nConstraints are not optional safety theatre. They are the last line of defence between your data model and chaos. Every NOT NULL, UNIQUE, and CHECK constraint you define today is a bug you will never have to debug tomorrow.

Why Constraints Beat Application Validation

Application-level validation can be bypassed: direct SQL access, a bug in one of ten microservices, a migration script that skips the ORM. Database constraints are enforced by the database engine for every write, from every source, always.

The Four Core Constraints

NOT NULL  → column must always have a value; NULL is not permitted
UNIQUE    → no two rows in the table may have the same value in this column
CHECK     → the expression must evaluate to TRUE for every row
DEFAULT   → if no value is supplied on INSERT, use this expression

What the beer_db Schema Already Enforces

Looking at beer_ratings:

score  numeric(3,1)  NOT NULL
  CHECK (score >= 1 AND score <= 10)

This means:

A NULL score is rejected immediately
A score of 0 or 11 is rejected immediately
The CHECK expression is evaluated on every INSERT and UPDATE
No application code or trigger is needed — the engine does it

Adding a Constraint After Table Creation

ALTER TABLE beers
  ADD CONSTRAINT beers_abv_check CHECK (abv >= 0 AND abv <= 25);

This runs a full table scan to validate all existing rows before the constraint is accepted. If any existing row violates it, the ALTER TABLE fails.

CHECK constraint

A constraint that evaluates a boolean SQL expression for every row. If the expression returns FALSE, the INSERT or UPDATE is rejected with an error. The expression can reference any columns in the same row.

UNIQUE constraint

Ensures that no two rows in the table have the same value in the constrained column(s). A UNIQUE constraint automatically creates an index, so it also speeds up lookups by that column. NULL values are treated as distinct from each other — two NULLs do not violate UNIQUE.

🔌 Connect to the Beer Database

Before exploring constraints, connect to the correct database. Run psql -U beer_db -d beer_db to open a psql session as the beer_db user.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

🔍 Read the Existing Constraints on beers

Run \d beers and read the Check constraints and Indexes sections carefully. Identify what is already protected and what might still be missing.

\d beers

Check constraints: "beers_abv_check" CHECK (abv >= 0::numeric AND abv <= 25::numeric) "beers_ibu_check" CHECK (ibu >= 0 AND ibu <= 200)

💥 Trigger a CHECK Constraint Violation

Attempt to insert a beer with abv = 30.0 (above the 25% maximum). First run SELECT id FROM breweries LIMIT 1; to get a valid brewery UUID, then insert a beer using that ID. Read the error message — constraint violations tell you exactly what went wrong.

SELECT id FROM breweries LIMIT 1;

INSERT INTO beers (brewery_id, name, style, abv) VALUES ((SELECT id FROM breweries LIMIT 1), 'Too Strong', 'IPA', 30.0);

ERROR: new row for relation "beers" violates check constraint "beers_abv_check" DETAIL: Failing row contains (..., 30.00, ...).

💥 Trigger a NOT NULL Violation

Attempt to insert a beer without a name. NOT NULL violations are a separate class of error from CHECK violations — read the difference in the error message.

This time, instead of copy-pasting the UUID from the previous step, use a subquery directly inside VALUES: (SELECT id FROM breweries LIMIT 1) fetches the UUID inline — no manual copy needed. The database runs the inner SELECT first and substitutes the result into the outer INSERT.

INSERT INTO beers (brewery_id, style, abv) VALUES ((SELECT id FROM breweries LIMIT 1), 'IPA', 5.5);

ERROR: null value in column "name" of relation "beers" violates not-null constraint DETAIL: Failing row contains (null, ...).

🔒 Witness the UNIQUE Constraint on Brewery Names

The brewery Golden Brewery already exists. Try to insert another brewery with that same name:

INSERT INTO breweries (name, city)
VALUES ('Golden Brewery', 'Brno');

The breweries_name_key UNIQUE constraint will reject the duplicate name. The city does not have to be unique; changing it to Brno does not avoid the name constraint.

INSERT INTO breweries (name, city) VALUES ('Golden Brewery', 'Brno');

ERROR: duplicate key value violates unique constraint "breweries_name_key" DETAIL: Key (name)=(Golden Brewery) already exists.

✅ Insert a Valid Beer Rating

Insert a valid beer rating with score = 8.5. Confirm it is accepted and the generated timestamp appears.

INSERT INTO beer_ratings (beer_id, reviewer_name, score) VALUES ((SELECT id FROM beers LIMIT 1), 'Test Reviewer', 8.5) RETURNING *;

INSERT 0 1

Lab complete! You can read, trigger, and interpret every core constraint type:\n\n\n NOT NULL → null value in column error ✅\n CHECK → violates check constraint error ✅\n UNIQUE → duplicate key value error ✅\n DEFAULT → auto-fills when omitted ✅\n

Enable JavaScript to run the live terminal and track your progress.