Column Constraints — NOT NULL / UNIQUE / CHECK / PRIMARY KEY

What column constraints are — blocking bad data at the DB layer

A column constraint (an input rule attached to a column) is a "condition on values that may go into this column," written into CREATE TABLE.

This article covers the four big ones — PRIMARY KEY (uniquely identifies a row),

NOT NULL (forbids NULL), UNIQUE (forbids duplicates), and CHECK (forbids values that don't satisfy a condition).

Unlike previous articles, no data is pre-loaded — instead, you build the tables yourself in the exercises and run constraint-violating INSERTs on purpose to experience the errors.

Declaring a table with constraints

Constraints go after the type in a column definition.

The shape is column_name type constraint constraint ..., and you can stack multiple constraints on one column.

Attaching PRIMARY KEY to an INTEGER column makes it the primary key that uniquely identifies a row.

NOT NULL forbids omitting the value, UNIQUE forbids the same value as another row, and CHECK (expression) forbids any value that doesn't make the expression true.

The member table below makes member_id the primary key, handle (a username) NOT NULL plus UNIQUE, and attaches a "non-negative" CHECK to age.

-- Sample of a table definition with constraints (just read it)
CREATE TABLE IF NOT EXISTS member (
  member_id INTEGER PRIMARY KEY,        -- primary key: unique and not NULL
  handle    TEXT    NOT NULL UNIQUE,    -- required and no duplicates
  email     TEXT    UNIQUE,             -- no duplicates (NULL allowed)
  age       INTEGER CHECK (age >= 0)    -- only non-negative values
);

-- A row that satisfies every constraint goes in without trouble
INSERT INTO member (member_id, handle, email, age)
VALUES (1, 'alice', 'alice@example.com', 30);

Imagine the requirement: "design a member table with constraints so that bad data can't get in." (Run it correctly and the explanation appears.)

① Start with DROP TABLE IF EXISTS app_member; to clear any previous version, then build the app_member table. Columns: member_id (integer, primary key), handle (text, required and no duplicates), email (text, no duplicates but NULL allowed), age (integer, non-negative only).

② INSERT one row that satisfies every constraint (e.g. member_id 1 / handle ascii string / email ascii string / age ≥ 0 integer).

③ Run SELECT * FROM app_member; to confirm that one row is in.

Constraint violations raise errors — NOT NULL and UNIQUE

A constraint's job is to reject violating writes.

Trying to put NULL (or omitting the value) into a NOT NULL column raises a NOT NULL constraint failed error, and inserting a value that already exists into a UNIQUE / PRIMARY KEY column raises a UNIQUE constraint failed error.

The INSERT row that errored does not enter the table.

-- NOT NULL violation: tries to put NULL into handle (just read it)
INSERT INTO member (member_id, handle, age) VALUES (2, NULL, 20);
--> Error: NOT NULL constraint failed: member.handle

-- UNIQUE violation: tries to insert the same handle as an existing row (just read it)
INSERT INTO member (member_id, handle, age) VALUES (3, 'alice', 25);
--> Error: UNIQUE constraint failed: member.handle

Imagine the requirement: "verify the effect of making handle required by triggering a violating INSERT." This exercise succeeds if you get an error (proof that the constraint is correctly blocking bad data).

① The leading DELETE FROM app_member; empties out app_member from Exercise 1 (so re-running gives the same result).

② Write an INSERT that puts NULL into the NOT NULL handle column on purpose (e.g. member_id 1 / handle NULL / email NULL / age 20).

③ Run it and confirm you get a NOT NULL constraint failed: app_member.handle error. Notice that the error message includes which table and which column was violated.

Imagine the requirement: "verify the effect of forbidding duplicates on handle by triggering a violating INSERT." This exercise also succeeds if you get an error.

① The leading DELETE FROM app_member; empties out app_member from Exercise 1.

② For row 1, write your own INSERT with handle set to 'alice' (e.g. member_id 1 / handle 'alice' / email 'alice@example.com' / age 30).

③ For row 2, write another INSERT with the same handle of 'alice' (you can change member_id and email).

④ Run it and confirm row 1 goes in fine, while row 2 raises UNIQUE constraint failed: app_member.handle.

CHECK constraints — express value conditions as expressions

CHECK (expression) is a constraint that uses an expression to describe what values may go into the column.

CHECK (age >= 0) rejects negative ages, CHECK (price > 0) rejects prices of 0 or below.

A frequent pattern is CHECK (status IN ('active','inactive')), which limits the set of allowed values.

If the expression doesn't evaluate to true, the INSERT / UPDATE is rejected with a CHECK constraint failed error.

This article's final exercises use CHECK (age >= 0) and CHECK (status IN (...)) to experience how CHECK rejects violations.

-- Restrict the range or set of values via CHECK (just read it)
CREATE TABLE IF NOT EXISTS product (
  product_id INTEGER PRIMARY KEY,
  price      INTEGER CHECK (price > 0),
  status     TEXT    CHECK (status IN ('active','inactive'))
);

-- price <= 0 is rejected with CHECK constraint failed
INSERT INTO product (product_id, price, status) VALUES (1, -100, 'active');

Imagine the requirement: "verify the effect of attaching a non-negative CHECK to age by triggering a violating INSERT." An error is the success condition.

① The leading DELETE FROM app_member; empties out app_member from Exercise 1.

② Write your own INSERT that puts a negative value (e.g. -5) into age. Since handle is NOT NULL, give it a non-NULL value.

③ Run it and confirm you get a CHECK constraint failed: app_member.age error. Optionally try changing age to a non-negative value and see that the INSERT then succeeds.

Imagine the requirement: "limit a member's status column to just the two values 'active' / 'inactive'." This is the article's final exercise — an error is the success condition.

① Start with DROP TABLE IF EXISTS app_member_status; to clear any previous version, then build the app_member_status table yourself. Two columns: member_id (integer, primary key) and status (text, required and only 'active' or 'inactive' allowed).

② As row 1, INSERT one of the allowed values ('active' or 'inactive').

③ As row 2, INSERT 'banned', which is not in the allowed list.

④ Run it and confirm row 1 goes through and row 2 raises CHECK constraint failed: app_member_status.status.