Constraints

LinkedIn Hook

"Your users table has 40,000 rows. 312 of them have the email NULL. 87 have the email " " (two spaces). 14 have the literal string "undefined". And one row has an age of -7."

Every one of those bad rows got in because somebody, somewhere, decided the application layer was "good enough" at validation. It was not. Application code gets rewritten, bypassed by scripts, and skipped by that one intern who wrote a direct INSERT for a data migration at 2 AM. The database is the last line of defense — and it only defends what you tell it to.

Constraints are how you tell it. NOT NULL says "this column is never empty." UNIQUE says "no two rows share this value." CHECK says "this value obeys this rule." DEFAULT says "if you forget, use this." Four simple tools, and together they turn a table from a bucket of strings into a contract the database enforces on every single write, forever.

In Lesson 1.3, I walk through column-level vs table-level syntax, naming conventions that save you in production, how to alter and drop constraints safely, and the Postgres quirks around UNIQUE with NULL that trip up half the candidates I interview.

Read the full lesson -> [link]

#Database #PostgreSQL #SQL #DataIntegrity #BackendDevelopment #InterviewPrep

---

---

What You'll Learn

• What NOT NULL, UNIQUE, CHECK, and DEFAULT actually do, and when each fires
• The difference between column-level and table-level constraint syntax, and why table-level wins for anything non-trivial
• How to name constraints so production error messages point at the exact rule that was violated
• How to add and drop constraints on a live table with ALTER TABLE
• Why UNIQUE in PostgreSQL allows multiple NULL values — and how that differs from MySQL and the SQL standard
• How CHECK constraints can span multiple columns, and why volatile functions inside them are a trap
• How DEFAULT expressions like now() and gen_random_uuid() work, and when defaults fire vs when they do not

---

The Nightclub Bouncer Analogy — Why Constraints Live in the Database

Picture a nightclub with a strict door policy. The owner posts the rules on a sign inside the office: must be 21 or older, must have a valid ID, no duplicate wristbands, and everyone gets a complimentary drink ticket on entry. Now imagine the only person enforcing those rules is the bartender, deep inside the club, after people are already on the dance floor. Some nights the bartender is busy. Some nights there is no bartender. Some nights a side door is propped open and people skip the line entirely. By 2 AM the club is full of underage drinkers, three people wearing the same wristband, and nobody has a drink ticket.

Now put a bouncer at the front door. The bouncer checks every person, every time, no exceptions — and the rules are written on a laminated card clipped to the bouncer's belt, not on an office wall nobody reads. An underage guest gets turned away at the door, not at the bar. A duplicate wristband never makes it inside. And every person who walks in gets a drink ticket pressed into their hand automatically.

That bouncer is a database constraint. The office sign is your application-level validation — it exists, it is well-intentioned, but it is easy to bypass. The database sits at the front door of your data. Every INSERT, every UPDATE, every COPY, every direct psql session from a sleepy engineer at 2 AM has to walk past the bouncer. NOT NULL checks ID. UNIQUE checks the wristband. CHECK verifies the age. DEFAULT hands out the drink ticket. Four bouncers, one door, zero exceptions.

+---------------------------------------------------------------+
|           APP-ONLY VALIDATION (The Problem)                   |
+---------------------------------------------------------------+
|                                                                |
|   [User]  ->  [API]  ->  [validate()]  ->  [DB: any value]    |
|                                [ skipped ]                     |
|                                    ^                           |
|                                    |                           |
|   [psql console]  ----------------'                            |
|   [migration script]  ------------'                            |
|   [admin dashboard]  -------------'                            |
|   [legacy cron job]  -------------'                            |
|                                                                |
|   Every non-API write bypasses validation.                     |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           DATABASE CONSTRAINTS (The Fix)                      |
+---------------------------------------------------------------+
|                                                                |
|   [User]  ------.                                              |
|   [psql]  ------+--> [DB: NOT NULL / UNIQUE / CHECK] -> table |
|   [script]  ----+                      ^                       |
|   [cron]  ------'                      |                       |
|                                 bouncer at the door            |
|                                 no path around it              |
|                                                                |
+---------------------------------------------------------------+

---

NOT NULL — The Cheapest Correctness Win You Will Ever Get

NOT NULL says: this column must always have a value. If an INSERT or UPDATE would leave it empty, the database rejects the whole statement. It is the single most underused constraint, and the reason is cultural — developers treat NULL as "I will fill this in later" and end up with tables where half the columns are optional for no real reason.

The rule of thumb: make every column NOT NULL by default, and only relax it when you have a specific business reason for the value to be unknown. A users.created_at column should never be NULL. A users.email column should never be NULL. A users.deleted_at column legitimately can be — the absence of a value means "not deleted."

-- Column-level NOT NULL: the constraint is attached to the column definition
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT        NOT NULL,   -- required, every user has one
    full_name   TEXT        NOT NULL,   -- required
    bio         TEXT,                   -- optional, NULL means "not provided"
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ             -- NULL means "not deleted"
);

-- Attempting to insert without a required column fails loudly
INSERT INTO users (email) VALUES ('alice@example.com');
-- ERROR:  null value in column "full_name" of relation "users"
--         violates not-null constraint

The payoff is huge. Queries no longer need COALESCE(full_name, '') everywhere. Indexes on NOT NULL columns are smaller. Aggregates do not silently skip rows. And most importantly, you cannot end up with a users row that has no name — the row simply refuses to exist.

---

UNIQUE — No Two Rows Share This Value

UNIQUE says: across the entire table, no two rows may have the same value (or the same combination of values) in this column or set of columns. It is how you enforce "one user per email," "one SKU per product," "one slug per blog post."

-- Column-level UNIQUE: simple, single-column uniqueness
CREATE TABLE products (
    id    BIGSERIAL PRIMARY KEY,
    sku   TEXT NOT NULL UNIQUE,       -- no two products share a SKU
    name  TEXT NOT NULL
);

-- Table-level UNIQUE: required when the uniqueness spans multiple columns
CREATE TABLE team_members (
    id        BIGSERIAL PRIMARY KEY,
    team_id   BIGINT NOT NULL,
    user_id   BIGINT NOT NULL,
    role      TEXT   NOT NULL,
    -- A user can be on multiple teams, but only once per team
    UNIQUE (team_id, user_id)
);

The Postgres NULL Quirk — Read This Twice

In PostgreSQL (and the SQL standard), NULL is not equal to anything, including another NULL. That means a UNIQUE constraint allows multiple rows with NULL in the unique column, because no two NULLs are considered duplicates.

CREATE TABLE invites (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE    -- NULL allowed, and multiple NULLs allowed
);

INSERT INTO invites (email) VALUES ('a@x.com');  -- OK
INSERT INTO invites (email) VALUES ('a@x.com');  -- ERROR: duplicate
INSERT INTO invites (email) VALUES (NULL);        -- OK
INSERT INTO invites (email) VALUES (NULL);        -- OK (!)
INSERT INTO invites (email) VALUES (NULL);        -- OK (!!)

This surprises almost every candidate in an interview. If you want "at most one NULL," PostgreSQL 15+ supports UNIQUE NULLS NOT DISTINCT, which treats NULLs as equal for uniqueness purposes:

-- PostgreSQL 15+: NULLs are treated as duplicates for this constraint
CREATE TABLE invites_v2 (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT,
    UNIQUE NULLS NOT DISTINCT (email)
);

MySQL note: MySQL (InnoDB) follows the same standard behavior as Postgres: UNIQUE allows multiple NULLs. The pre-15 Postgres workaround — a partial unique index on WHERE email IS NOT NULL plus a second partial index — works in both, but Postgres 15's NULLS NOT DISTINCT is cleaner.

Partial Unique Indexes — The Real-World Pattern

Sometimes uniqueness is conditional. "Email must be unique, but only among active users" is a classic. Plain UNIQUE cannot express that — but a partial unique index can, and in Postgres it is the idiomatic tool for this job:

-- Only active (non-deleted) users must have unique emails.
-- Soft-deleted users can keep their old email in the row.
CREATE UNIQUE INDEX users_active_email_unique
    ON users (email)
    WHERE deleted_at IS NULL;

MySQL does not support partial indexes. The common workaround there is a generated column or a trigger.

---

CHECK — Custom Rules, Enforced on Every Write

CHECK lets you write a boolean expression that must be true for every row. If the expression is FALSE or NULL for a given row on insert or update, the statement is rejected. It is the escape hatch for anything the other three constraints cannot express.

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    quantity    INT     NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC NOT NULL CHECK (unit_price >= 0),
    discount    NUMERIC NOT NULL DEFAULT 0
                CHECK (discount >= 0 AND discount <= 1),
    status      TEXT    NOT NULL
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

Every column above has its own CHECK, attached inline. This is column-level CHECK — it can only reference the single column it is attached to.

Multi-Column CHECK — Use Table-Level Syntax

When a rule spans more than one column, you must write the CHECK at the table level, after all the column definitions:

CREATE TABLE bookings (
    id          BIGSERIAL PRIMARY KEY,
    room_id     BIGINT      NOT NULL,
    starts_at   TIMESTAMPTZ NOT NULL,
    ends_at     TIMESTAMPTZ NOT NULL,
    guest_count INT         NOT NULL,
    max_guests  INT         NOT NULL,

    -- Table-level CHECK: references multiple columns
    CHECK (ends_at > starts_at),
    CHECK (guest_count <= max_guests),
    CHECK (guest_count > 0)
);

Table-level checks are easier to read, easier to name, and they let you reference any column in the table. My recommendation: always use table-level syntax for CHECK constraints, even single-column ones, the moment you start naming them.

Named Constraints — The Single Most Important Habit

Unnamed constraints get auto-generated names like orders_quantity_check1. When that constraint fails in production at 3 AM, the error message says:

ERROR:  new row for relation "orders" violates check constraint "orders_quantity_check1"

That tells you nothing. Was it the quantity check? The price check? The status check? You have to open the schema and count. Contrast with a named constraint:

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    quantity   INT     NOT NULL,
    unit_price NUMERIC NOT NULL,
    discount   NUMERIC NOT NULL DEFAULT 0,
    status     TEXT    NOT NULL,

    CONSTRAINT orders_quantity_positive_chk     CHECK (quantity > 0),
    CONSTRAINT orders_unit_price_nonneg_chk     CHECK (unit_price >= 0),
    CONSTRAINT orders_discount_range_chk        CHECK (discount BETWEEN 0 AND 1),
    CONSTRAINT orders_status_valid_chk
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

Now the error becomes:

ERROR:  new row for relation "orders" violates check constraint
        "orders_discount_range_chk"

That error tells you the exact rule in one glance. It also means you can ALTER TABLE ... DROP CONSTRAINT orders_discount_range_chk without guessing the auto-generated name.

A common naming convention is <table>_<column(s)>_<rule>_<type>, where type is one of pk, fk, uq, chk, nn, df. Pick a convention on day one and stick to it — your future self opening a production error dashboard will thank you.

+---------------------------------------------------------------+
|           CONSTRAINT NAMING CONVENTION                        |
+---------------------------------------------------------------+
|                                                                |
|   <table>_<column>_<rule>_<type>                               |
|                                                                |
|   pk  = primary key                                            |
|   fk  = foreign key                                            |
|   uq  = unique                                                 |
|   chk = check                                                  |
|   nn  = not null (rare, usually unnamed)                       |
|   df  = default (rare, defaults have no name)                  |
|                                                                |
|   Examples:                                                    |
|     users_email_uq                                             |
|     orders_quantity_positive_chk                               |
|     bookings_date_range_chk                                    |
|     team_members_team_user_uq                                  |
|                                                                |
+---------------------------------------------------------------+

---

DEFAULT — The Value You Get When You Do Not Supply One

DEFAULT is not technically a constraint in the same sense as the others — it does not reject bad data, it fills in a value when none is provided. But it pairs so tightly with NOT NULL that it belongs in the same mental toolbox. The pattern col TYPE NOT NULL DEFAULT <expr> is one of the most common things you will write.

CREATE TABLE audit_log (
    id          UUID        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    actor_id    BIGINT      NOT NULL,
    action      TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    metadata    JSONB       NOT NULL DEFAULT '{}'::jsonb,
    ip_address  INET                 DEFAULT '0.0.0.0'::inet
);

-- Insert only the required business fields; defaults fill the rest
INSERT INTO audit_log (actor_id, action)
VALUES (42, 'login');

-- Result: a row with a fresh UUID id, now() for created_at, empty JSONB, and
-- '0.0.0.0' for ip_address.

A DEFAULT expression can be:

• A literal: DEFAULT 0, DEFAULT '', DEFAULT 'pending'
• A function call: DEFAULT now(), DEFAULT gen_random_uuid(), DEFAULT current_user
• A computed expression: DEFAULT (random() * 100)::int

The function is evaluated once per row at insert time, not once when the table is created. That is why DEFAULT now() gives every row its own timestamp rather than the timestamp of the CREATE TABLE.

When DEFAULT Does Not Fire

A default fires only when the column is not mentioned in the INSERT, or when the column is explicitly set to DEFAULT. It does not fire when you pass NULL:

-- created_at gets now() because the column is omitted
INSERT INTO audit_log (actor_id, action) VALUES (1, 'x');

-- created_at gets now() because we explicitly ask for the default
INSERT INTO audit_log (actor_id, action, created_at)
VALUES (1, 'x', DEFAULT);

-- created_at is NULL here -- and if the column is NOT NULL, this FAILS.
-- The default is NOT used as a fallback for an explicit NULL.
INSERT INTO audit_log (actor_id, action, created_at)
VALUES (1, 'x', NULL);
-- ERROR: null value in column "created_at" violates not-null constraint

This catches people constantly. NULL and "not provided" are different things to SQL, and defaults only cover the second case.

---

Column-Level vs Table-Level Syntax

Every constraint except NOT NULL and DEFAULT can be written at either the column level (inline after a column definition) or the table level (in its own clause after all column definitions). The two forms are equivalent for single-column constraints. The table-level form becomes mandatory once the constraint spans multiple columns.

-- COLUMN-LEVEL: concise for simple, single-column rules
CREATE TABLE accounts_v1 (
    id       BIGSERIAL PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    balance  NUMERIC NOT NULL CHECK (balance >= 0)
);

-- TABLE-LEVEL: required for multi-column rules, preferred when naming
CREATE TABLE accounts_v2 (
    id       BIGSERIAL,
    email    TEXT    NOT NULL,
    balance  NUMERIC NOT NULL,

    CONSTRAINT accounts_pk              PRIMARY KEY (id),
    CONSTRAINT accounts_email_uq        UNIQUE (email),
    CONSTRAINT accounts_balance_nonneg_chk CHECK (balance >= 0)
);

Rule of thumb: use column-level for tiny scripts and throwaway tables, table-level for anything that will end up in production. Table-level syntax gives you named constraints, multi-column expressions, and a clean place to read all the rules of a table at a glance.

+---------------------------------------------------------------+
|           COLUMN-LEVEL vs TABLE-LEVEL                         |
+---------------------------------------------------------------+
|                                                                |
|   COLUMN-LEVEL:                                                |
|     col TYPE [NOT NULL] [UNIQUE] [CHECK(...)] [DEFAULT ...]   |
|                                                                |
|     + Concise                                                  |
|     - Cannot reference other columns                           |
|     - Hard to name cleanly                                     |
|                                                                |
|   TABLE-LEVEL:                                                 |
|     CONSTRAINT name UNIQUE (col1, col2)                        |
|     CONSTRAINT name CHECK (col1 < col2)                        |
|                                                                |
|     + Can span multiple columns                                |
|     + Easy to name                                             |
|     + Clean for complex rules                                  |
|                                                                |
|   NOT NULL and DEFAULT are always column-level.                |
|                                                                |
+---------------------------------------------------------------+

---

Altering and Dropping Constraints on a Live Table

Schemas evolve. A column that was nullable on day one needs to become required. A CHECK rule gets stricter. A UNIQUE gets dropped when the business rule changes. ALTER TABLE is the tool, and the syntax is predictable once you know the three verbs: ADD CONSTRAINT, DROP CONSTRAINT, and ALTER COLUMN ... SET/DROP.

-- Add a NOT NULL to an existing column. Fails if any row currently has NULL.
ALTER TABLE users
    ALTER COLUMN phone SET NOT NULL;

-- Drop a NOT NULL (makes the column nullable again)
ALTER TABLE users
    ALTER COLUMN phone DROP NOT NULL;

-- Add a DEFAULT (only affects future inserts; existing rows are untouched)
ALTER TABLE users
    ALTER COLUMN status SET DEFAULT 'active';

-- Drop a DEFAULT
ALTER TABLE users
    ALTER COLUMN status DROP DEFAULT;

-- Add a named CHECK constraint
ALTER TABLE orders
    ADD CONSTRAINT orders_quantity_positive_chk CHECK (quantity > 0);

-- Add a named UNIQUE constraint (single or multi-column)
ALTER TABLE team_members
    ADD CONSTRAINT team_members_team_user_uq UNIQUE (team_id, user_id);

-- Drop any named constraint by name
ALTER TABLE orders
    DROP CONSTRAINT orders_quantity_positive_chk;

The Validation Trap — and NOT VALID

When you add a CHECK or a foreign key to a table with millions of rows, Postgres will scan the entire table to confirm every existing row satisfies the rule. On a big table this can lock writes for minutes. The escape hatch is NOT VALID:

-- Step 1: add the constraint without scanning existing rows.
-- New and updated rows will be checked; old rows are grandfathered in.
ALTER TABLE orders
    ADD CONSTRAINT orders_quantity_positive_chk
    CHECK (quantity > 0) NOT VALID;

-- Step 2: later, validate the old rows in the background.
-- This only takes a SHARE UPDATE EXCLUSIVE lock (allows reads and writes).
ALTER TABLE orders
    VALIDATE CONSTRAINT orders_quantity_positive_chk;

This two-step pattern is how you add constraints to a large production table without downtime.

MySQL note: MySQL has a similar idea via ALTER TABLE ... ADD CONSTRAINT ... NOT ENFORCED, though semantics differ. Always test migrations on a realistic-size dataset before running them in production.

---

Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Four vertical columns labeled NOT NULL, UNIQUE, CHECK, DEFAULT across the top in sky blue (#4fc3f7) monospace. Each column shows a small example row being validated: a red rose (#ff5c8a) X through a NULL value under NOT NULL, a red X through a duplicate row under UNIQUE, a red X through 'age = -7' under CHECK, and a sky-blue checkmark with 'now()' auto-filling a timestamp under DEFAULT. At the bottom, an amber arrow labeled 'ALTER TABLE ADD CONSTRAINT ... NOT VALID -> VALIDATE' pointing from a small table icon to a large table icon. White monospace labels throughout."

---

Common Mistakes

1. Leaving constraints unnamed. Auto-generated names like users_email_check1 turn every production error into a schema-archaeology exercise. Name every non-trivial constraint with CONSTRAINT <table>_<column>_<rule>_<type> so the error message tells you the exact rule that failed. Named constraints are also the only way to drop them cleanly — otherwise you have to query pg_constraint to find what Postgres decided to call them.

2. Relying on the application layer for data integrity. "The API validates it" is only true for writes that go through the API. Data migration scripts, admin consoles, backfill jobs, bulk imports, and direct psql sessions all bypass application validation. The database is the one place every write has to pass through — put your invariants there. Application validation is still valuable for user-friendly errors, but it is a first line of defense, not the only one.

3. Forgetting NOT NULL on columns that should never be empty. The default in SQL is nullable, and too many developers leave it that way out of laziness. A created_at without NOT NULL is a bug waiting to happen — one INSERT from an ORM that forgets the column and you end up with a row that has no creation date. Make NOT NULL your default; make nullability the exception you justify.

4. Using DEFAULT as a fallback for explicit NULL. DEFAULT only fires when a column is omitted from the insert, not when you pass NULL explicitly. If you want "fall back to the default when the caller passes NULL," you need COALESCE in application code, a BEFORE INSERT trigger, or a generated column. The raw DEFAULT clause will not save you — and if the column is also NOT NULL, an explicit NULL insert will fail.

5. Putting volatile functions inside CHECK constraints. A CHECK constraint must be deterministic — given the same row, it must always return the same answer. Using now(), random(), or a subquery inside a CHECK breaks that guarantee, and Postgres actively forbids most of it (CHECK (created_at <= now()) will be rejected or produce confusing errors). If you need a time-based rule, enforce it with a trigger, not a CHECK. The only functions safe inside CHECK are pure, immutable ones like length(), abs(), or regex matches.

---

Interview Questions

1. "What is the difference between a column-level and a table-level constraint, and when would you prefer one over the other?"

A column-level constraint is written inline as part of a column's definition — for example, email TEXT NOT NULL UNIQUE. It applies to exactly that one column and cannot reference any other column. A table-level constraint is written as a separate clause after all column definitions, using syntax like CONSTRAINT accounts_email_uq UNIQUE (email) or CONSTRAINT bookings_date_range_chk CHECK (ends_at > starts_at). Table-level constraints can span multiple columns, can be given a readable name directly in the CREATE TABLE statement, and read more clearly when you have several rules to enforce. I prefer column-level syntax only for tiny scripts and NOT NULL/DEFAULT (which are always inline). For anything production-bound, I use table-level syntax so I can name every constraint — because when a constraint fails at 3 AM, a name like orders_discount_range_chk tells you exactly what happened, while an auto-generated name like orders_check1 tells you nothing.

2. "How does PostgreSQL's UNIQUE constraint handle NULL values? How is that different from what most developers expect?"

PostgreSQL follows the SQL standard: NULL is not equal to anything, including another NULL. Because a UNIQUE constraint only rejects rows that are "equal" to an existing row, multiple rows with NULL in the unique column are perfectly legal. You can insert NULL, NULL, NULL into a UNIQUE column ten times and none of them conflict. Most developers expect UNIQUE to mean "at most one NULL," and are surprised the first time their "unique email" column ends up with five rows of NULL. There are two fixes. In PostgreSQL 15 and later, you can write UNIQUE NULLS NOT DISTINCT (email) to treat NULLs as duplicates. Before that, the idiomatic pattern is a partial unique index: CREATE UNIQUE INDEX ... ON users(email) WHERE email IS NOT NULL, which enforces uniqueness only for non-null rows. MySQL's InnoDB behaves the same as standard Postgres — multiple NULLs allowed in a UNIQUE column.

3. "You need to add a NOT NULL and a CHECK constraint to a table with 500 million rows in production. Walk me through how you would do it safely."

The naive approach — ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) — takes an ACCESS EXCLUSIVE lock and scans every row to validate. On 500 million rows, that blocks all reads and writes for minutes or hours. The safe approach in PostgreSQL is the two-step NOT VALID pattern. First, add the constraint with NOT VALID: ALTER TABLE orders ADD CONSTRAINT orders_quantity_positive_chk CHECK (quantity > 0) NOT VALID. This takes a brief lock and immediately starts enforcing the rule for all new inserts and updates, but skips the scan of existing rows. Then, in a separate command, run ALTER TABLE orders VALIDATE CONSTRAINT orders_quantity_positive_chk, which scans existing rows under a much weaker SHARE UPDATE EXCLUSIVE lock that allows concurrent reads and writes. For NOT NULL, the modern Postgres equivalent is to first add a CHECK (col IS NOT NULL) NOT VALID, validate it, and then swap it for a true NOT NULL — or in Postgres 12+, rely on the optimization where an existing validated CHECK (col IS NOT NULL) lets ALTER COLUMN SET NOT NULL skip the scan. Always test on a realistic staging dataset before running in production, and have a rollback plan ready.

4. "A CHECK constraint and application-level validation both reject invalid data. Why bother with the database constraint?"

Because the application is not the only thing that writes to the database. Consider every source of writes in a typical system: the main API, a background job runner, a data migration script, a nightly analytics import, an admin dashboard, a CSV bulk-upload tool, and the engineer who opens psql at midnight to fix a production issue. Application validation only runs for writes that go through the application. Every other path — and there are always other paths — bypasses it. A database CHECK sits at the lowest layer and runs on every single write, no matter which client made it. That makes it the only place you can truly guarantee "this column never contains a negative number" or "this status is always one of four valid values." Application validation is still worth having, because it produces friendly error messages and catches problems before a round-trip to the database, but it is a convenience layer, not a correctness layer. The database is the correctness layer.

5. "What does DEFAULT do when a column is explicitly set to NULL in an INSERT?"

Nothing. DEFAULT only fires when the column is omitted from the INSERT column list, or when the value DEFAULT is explicitly supplied. If you write INSERT INTO t (created_at) VALUES (NULL), the database takes that NULL literally — it does not substitute the default. This catches people all the time, especially when a caller passes NULL meaning "use whatever the default is." If the column is also NOT NULL, the insert fails with a not-null constraint violation instead of silently using the default. If you need "fall back to default when NULL is passed," you have to handle it in application code using COALESCE, or write a BEFORE INSERT trigger that replaces NULL with the default value. The raw DEFAULT clause is strictly a "no value provided" fallback, not a "null coalescing" operator.

---

Quick Reference — Constraints Cheat Sheet

+---------------------------------------------------------------+
|           CONSTRAINTS CHEAT SHEET                             |
+---------------------------------------------------------------+
|                                                                |
|  NOT NULL  -> value must be present on every write            |
|               col TYPE NOT NULL                                |
|                                                                |
|  UNIQUE    -> no two rows share the value(s)                  |
|               col TYPE UNIQUE                                  |
|               UNIQUE (col1, col2)                              |
|               NULLs allowed, multiple NULLs allowed (PG/MySQL) |
|               PG 15+: UNIQUE NULLS NOT DISTINCT (col)          |
|                                                                |
|  CHECK     -> boolean rule per row                             |
|               CHECK (col > 0)                                  |
|               CHECK (col1 < col2)   -- multi-col: table-level  |
|               Must be deterministic (no now(), no random())   |
|                                                                |
|  DEFAULT   -> value used when column omitted from INSERT      |
|               DEFAULT 0                                        |
|               DEFAULT now()                                    |
|               DEFAULT gen_random_uuid()                        |
|               Does NOT fire on explicit NULL                   |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           ALTER TABLE QUICK REFERENCE                         |
+---------------------------------------------------------------+
|                                                                |
|  ALTER TABLE t ALTER COLUMN c SET  NOT NULL;                   |
|  ALTER TABLE t ALTER COLUMN c DROP NOT NULL;                   |
|  ALTER TABLE t ALTER COLUMN c SET  DEFAULT <expr>;             |
|  ALTER TABLE t ALTER COLUMN c DROP DEFAULT;                    |
|                                                                |
|  ALTER TABLE t ADD  CONSTRAINT name UNIQUE (c1, c2);           |
|  ALTER TABLE t ADD  CONSTRAINT name CHECK (c > 0);             |
|  ALTER TABLE t ADD  CONSTRAINT name CHECK (c > 0) NOT VALID;   |
|  ALTER TABLE t VALIDATE CONSTRAINT name;                       |
|  ALTER TABLE t DROP CONSTRAINT name;                           |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           NAMING CONVENTION                                    |
+---------------------------------------------------------------+
|                                                                |
|   <table>_<column(s)>_<rule>_<type>                            |
|                                                                |
|   users_email_uq                                               |
|   orders_quantity_positive_chk                                 |
|   orders_status_valid_chk                                      |
|   bookings_date_range_chk                                      |
|   team_members_team_user_uq                                    |
|                                                                |
+---------------------------------------------------------------+

Constraint	What It Rejects	Multi-Column?	Typical Use
NOT NULL	Rows where the column is NULL	No (column-only)	Required fields
UNIQUE	Rows that duplicate existing value(s)	Yes (table-level)	Emails, SKUs, slugs
CHECK	Rows where the expression is FALSE	Yes (table-level)	Enums, ranges, invariants
DEFAULT	Nothing — it fills, does not reject	No (column-only)	Timestamps, UUIDs, status
PRIMARY KEY	NULL or duplicates (covered in Ch 2)	Yes	Row identity
FOREIGN KEY	References that do not exist (covered in Ch 2)	Yes	Referential integrity

Coming in Chapter 2: PRIMARY KEY and FOREIGN KEY get their own lesson, including ON DELETE CASCADE, deferrable constraints, and how foreign keys interact with indexes.

---

Prev: Lesson 1.2 -- Data Types Next: Lesson 1.4 -- Schema Design Basics

---

This is Lesson 1.3 of the Database Interview Prep Course -- 12 chapters, 58 lessons.