Topic: PostgreSQL

A Database Is Not a JSON Blob: Let Constraints Protect Data

Tables, keys, and constraints form a shared data contract. Frontend validation improves UX; the database must reject impossible writes.

At the beginning of a project, nearly everything looks easy to put in a JSON column: one object for a user, one for an order, another for settings. A few weeks later the questions appear. Who is allowed to write it? Can an email be duplicated? Can an order belong to a user who does not exist? Can a price be negative?

Those are not small ORM, form, or API details. They are data-modeling work. The most practical benefit of a relational database is not merely that it accepts SQL. It lets the system keep “which data is impossible” close to the data, so an API, an admin tool, a scheduled job, and a future service all follow the same rule.

This starts with tables, rows, and columns. The goal is not memorizing DDL keywords. It is learning an order of thought: describe the facts first, then let the database enforce facts that cannot be broken.

Tables, rows, and columns: say what the data is

Treat a customer as one entity. A customers table stores every customer; each row is one concrete customer; each column is a fact about that customer, such as a name, email address, or creation time.

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

This is more explicit than a JSON object, but it answers important questions:

• id is a stable identity for the row. It does not change when a display name or email changes.
• email must be present and cannot be duplicated.
• name must be present; a length or format limit is a separate, explicit business rule.
• created_at has a creation time even when an application forgets to send one.

A schema does not freeze the future. It states the rules known today. A changed rule can be migrated; an unstated rule is merely postponed until the data is already messy.

A primary key is identity; UNIQUE is a separate business rule

A primary key means every row has a unique, non-null identifier. Other tables can reliably point to that row, and updates, deletes, and debugging have a stable target.

An email often deserves UNIQUE, but it is not always a good primary key. People change email addresses; case and verification policies evolve; an internal id should not drift with them. The division is useful: a stable key identifies a row, while UNIQUE says a specific business value must not collide.

Do not add UNIQUE to every column. Whether phone numbers, names, or company names may repeat is a real business decision. A constraint is a rule, not decoration.

A foreign key stops an order from naming a customer that does not exist

An order belongs to a customer. Make that relationship a customer_id column and add a foreign key:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

The database now rejects an order whose customer_id names no customer, and it rejects a negative total_cents. That is more than saving two backend if statements. The rule survives an admin tool, an import job, or a different service writing to the same database.

PostgreSQL creates an index for a PRIMARY KEY or UNIQUE constraint, but it does not automatically create one for the referencing foreign-key column such as orders.customer_id. Add that index when real queries, joins, or customer deletions need it. It is a later performance decision to measure, not a ritual to guess today.

NOT NULL, CHECK, and DEFAULT: state what cannot be ambiguous

NULL is not an empty string or zero. It means no value or an unknown value. If an email must exist, use NOT NULL. To find rows without a value, SQL uses IS NULL, not = NULL.

CHECK makes a value satisfy a condition, such as a non-negative amount or a rating between 1 and 5. DEFAULT supplies a reasonable fact when a value is omitted, such as the creation time. Their jobs are distinct:

Keeping these in the table definition is easier to review than scattering them through controllers, forms, and cron jobs. It also does not demand a perfect first design. Add rules for facts that are already known, then evolve them with the product.

Frontend validation and database constraints are not rivals

Frontend validation still matters. It can promptly tell someone that an email format is invalid or an amount is missing, rather than waiting for a submission error. That is good UX.

But the frontend is not the final integrity boundary. Someone can call an API directly, an old application version can still run, and imports or jobs can skip a form altogether. A database constraint says that whichever path writes the data, an impossible record cannot enter.

The clean division is: the frontend gives an early, friendly prompt; the API returns a clear error; the database guarantees the fact. The checks may overlap, but they have different responsibilities.

A practical modeling order

When creating a table, use this six-step start:

1. Find nouns: customers, orders, articles, and comments are often candidate tables.
2. List facts: decide which columns each entity actually needs and their sensible types.
3. Choose a stable identity: use a primary key, not a display value that can change.
4. State invariants: use NOT NULL, UNIQUE, CHECK, and DEFAULT for confirmed rules.
5. Connect relationships: use foreign keys for belonging and references.
6. Then add UI and API validation so errors arrive earlier and make sense to people.

This is not a grand database-design methodology. It is a starting point that keeps basic rules from being missed. Once tables multiply, normalization, transactions, indexes, and query plans have a clearer place to land.

What I learned

• I can state an entity’s identity, required facts, and relationships before rushing into application code.
• A primary key identifies a stable row, while UNIQUE, NOT NULL, and CHECK each express a different invariant.
• Frontend validation reduces friction, but only database constraints give every writing path the same integrity guarantee.
• An index is a performance choice based on real query patterns, not an automatic add-on for every foreign key.

Conclusion: stop invalid data before it becomes history

A database schema does not need to be complicated on day one. One clear table with a stable primary key, necessary NOT NULL, appropriate UNIQUE, CHECK, and foreign-key rules already removes a great deal of future guesswork.

JSON is fine as a transport format. Treating a database as a giant JSON bucket with no rules is what makes every piece of code guess the rules again. Let constraints reject impossible data first; then features have a foundation they can trust.

External references

• PostgreSQL Tutorial
• PostgreSQL: Data Definition and Constraints
• PostgreSQL: CREATE TABLE
• daily.dev: How a SQL database works
• daily.dev: Creating Foreign Keys — SQL Fundamentals with PostgreSQL
• Reddit: DBMS: What’s the use of defining a primary key?
• Reddit: How senior engineers design production databases

Further learning

• CS50 SQL Week 1: Relating is a video-based introduction that connects tables, primary keys, foreign keys, and joins.
• CS50 SQL Lecture 2 notes reviews schemas, constraints, and normalization—the context for why each rule in this article exists.