Relational Modeling and Constraints

1. What Is It?

The relational model organizes data into tables (relations), where each row represents one entity instance and each column represents an attribute of that entity. Relationships between entities are expressed through shared keys rather than physical pointers or nested structures.

Constraints are rules enforced by the database engine that prevent invalid data from being written. Without constraints, your application code becomes the only line of defense against bad data — and application code has bugs, gets bypassed by scripts, and doesn't run inside transactions initiated by other services. Constraints encode data rules once, at the storage layer, and enforce them unconditionally.

2. How It Works

Consider an e-commerce schema: users, orders, and order_items.

Copy
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id),
    status      TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id          BIGSERIAL PRIMARY KEY,
    order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku         TEXT NOT NULL,
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price_cents BIGINT NOT NULL CHECK (unit_price_cents >= 0)
);

What the database enforces here:

• PRIMARY KEY — every row has a unique, non-null identifier; the engine creates an index automatically
• NOT NULL — the column must have a value; prevents "empty" rows that break application logic
• UNIQUE — no two rows can share this value; email being unique means no duplicate accounts
• FOREIGN KEY — orders.user_id must match an existing users.id; prevents orphaned orders
• CHECK — arbitrary boolean expression; status IN (...) prevents undocumented status strings
• ON DELETE CASCADE — when an order is deleted, its items are automatically deleted too

-+ResetFullscreen

When an INSERT or UPDATE violates a constraint, the database raises an error, rolls back the statement, and your application receives an exception. The data is never written.

3. What SDEs Actually Need to Know

Store money as integers, not floats. total_cents BIGINT instead of total FLOAT. Floating-point arithmetic accumulates rounding errors; integers are exact. Divide by 100 only at display time.

Foreign key constraints have a performance cost at write time. Each insert to orders triggers a lookup in users to verify user_id exists. At high insert volume this becomes measurable. Some teams disable FK enforcement and enforce referential integrity at the application layer — a tradeoff that trades write speed for the risk of orphaned data.

ON DELETE behavior choices:

• CASCADE — delete child rows when parent is deleted (use for owned entities like order_items)
• RESTRICT / NO ACTION — block deletion if children exist (default; use when the parent shouldn't be deleted while children reference it)
• SET NULL — null out the FK column (use when the child can exist without a parent, e.g., a post whose author was deleted)

CHECK constraints don't fire on NULLs. CHECK (quantity > 0) passes when quantity IS NULL because NULL > 0 evaluates to NULL (not false). Add NOT NULL separately if the column must have a value.

Common error messages and what they mean:

• violates not-null constraint → your code sent NULL for a required field
• violates unique constraint → duplicate value on a unique column; handle in code with upsert or by catching the error
• violates foreign key constraint → referencing a row that doesn't exist, or trying to delete a parent that still has children

4. Tradeoffs & Decisions

Enforce in the database vs. enforce in application code

The practical answer: use both. Application validation gives users fast, specific error messages. Database constraints are the backstop that prevents corrupt data when the application has bugs.

Nullable vs. non-nullable columns

Defaulting everything to nullable feels safe but creates problems downstream: every query that reads the column must handle NULL, JOIN conditions silently drop rows, and aggregate functions skip NULLs in ways that surprise developers. Make columns NOT NULL unless you have a specific reason a value can be absent.

Surrogate vs. natural keys as primary keys

• Surrogate key (e.g., BIGSERIAL id) — meaningless integer, stable across renames, simple FK references
• Natural key (e.g., email, order_number) — carries meaning but can change, is longer (worse index performance), and leaks internal IDs in URLs

Most applications use surrogate keys for PKs and add UNIQUE constraints on natural identifiers.

5. Interview Cheat Sheet

Key sentences:

• "The relational model enforces data integrity through constraints at the storage layer, not just in application code."
• "A foreign key constraint guarantees referential integrity: you can't have an order for a user that doesn't exist."
• "NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints each catch a different class of invalid data before it's persisted."
• "I store monetary values as integer cents to avoid floating-point rounding errors."

Common follow-ups:

Q: What happens if you remove a foreign key constraint for performance? A: Writes get faster (no FK lookup), but you trade correctness guarantees for speed. Orphaned rows can accumulate, breaking JOIN queries and causing null values in your application. You need compensating application logic and periodic consistency checks.

Q: Why would a UNIQUE constraint fail on a column you know has distinct values? A: NULL handling. A UNIQUE constraint allows multiple NULLs in most databases (PostgreSQL, MySQL) because NULL != NULL. If you're seeing unexpected uniqueness violations, check for an existing race condition or a bug that sent the same value twice in a concurrent path.

Q: What's the difference between ON DELETE CASCADE and ON DELETE RESTRICT? A: CASCADE removes children automatically when the parent is deleted — appropriate when children are owned by the parent and have no meaning without it. RESTRICT (the default) blocks deletion of the parent if any children still reference it — appropriate when the child relationship should prevent accidental deletion of the parent.