Use primary keys and foreign keys to preserve an order

An order should not outlive its customer reference accidentally, and a line item should not point to an order that never existed. A primary key gives a row an unambiguous identity. A foreign key makes another table’s stored reference obey that identity. Together they protect relationships even when a worker or maintenance script bypasses the HTTP handler.

For a developer who can already write an insert and a select, the next step is to read a schema as a set of permitted and rejected operations. This small PostgreSQL example models customers, orders and numbered lines. It also demonstrates an important limit: referential integrity does not decide whether deleting a paid order is allowed by the business.

Model identity at the right scope

Run the following in an empty PostgreSQL database. The example supplies explicit integer identifiers so the relationships are easy to inspect; it does not implement identifier allocation.

CREATE TABLE customers (
  id bigint PRIMARY KEY,
  display_name text NOT NULL
);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  state text NOT NULL DEFAULT 'draft' CHECK (state IN ('draft', 'paid'))
);
CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  line_no integer NOT NULL CHECK (line_no > 0),
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)
);

The customer and order primary keys are unique and non-null. The line key has two columns because line one belongs to a particular order. Order 418 can have line one, and order 419 can also have line one. Neither order can contain two rows with the same line number.

This key does not require consecutive numbering. Lines one and three are legal even if line two is missing. It also does not make the SKU unique within an order. Two separate lines can name the same SKU if they have different line numbers. Those are separate product decisions, so avoid reading more into the composite key than it says.

The customer reference is mandatory because of NOT NULL. A simple nullable foreign key would permit a null reference while still rejecting a non-null identifier with no parent. A foreign key and a not-null constraint answer different questions: whether a supplied relationship is valid, and whether the relationship must be supplied.

The PostgreSQL constraints reference specifies the unique, non-null primary-key rule and the requirements for referenced columns. Here every foreign key points to a primary key. PostgreSQL also supports appropriate unique constraints or non-partial unique indexes as targets; an ordinary non-unique index is not enough.

The SKU is deliberately plain text. There is no product table, current price, inventory reservation or authorization rule in this schema. Storing a string that looks like a product identifier does not enforce a product relationship. A real order might retain a historical SKU snapshot, a product reference or both, depending on what must survive catalog changes.

Insert one small, inspectable order history

Load two customers, a draft order, a paid order and three lines:

INSERT INTO customers VALUES (73, 'Aster Studio'), (74, 'Unused customer');
INSERT INTO orders (id, customer_id, state)
VALUES (418, 73, 'draft'), (419, 73, 'paid');
INSERT INTO order_items VALUES
  (418, 1, 'CAMERA', 1), (418, 2, 'BATTERY', 2), (419, 1, 'MIC', 1);
SELECT order_id, line_no, sku, quantity
FROM order_items ORDER BY order_id, line_no;

The result contains (418, 1), (418, 2) and (419, 1). The last row shows the scope of the composite identity. Customer 74 has no orders and will be useful for a later concurrency exercise.

View full-size image ↗

The diagram distinguishes a relationship from its deletion policy. It does not imply that customers own every aspect of an order’s business lifecycle, or that a paid state makes the order undeletable.

Try rejected operations individually in autocommit, or use a savepoint when experimenting inside a transaction. A failed statement normally leaves an explicit PostgreSQL transaction needing rollback before it can continue. Do not paste a series of expected failures into a migration and assume later statements will still run.

Attempt against the loaded data	Expected SQLSTATE	Why it fails
Insert order 420 for customer 999	23503	Referenced customer is absent
Insert order 420 with a null customer	23502	Customer is mandatory
Insert another order 418	23505	Order identity already exists
Insert another line (418, 1)	23505	That line identity already exists
Insert a line for order 999	23503	Referenced order is absent
Insert line zero or quantity zero	23514	A positive-value check fails
Delete customer 73	23503	Existing orders still reference it

The local PostgreSQL 17.11 checks executed these cases and verified that the failed operations left the original two customers, two orders and three lines intact. They also accepted an unfamiliar SKU string, confirming that this column is not a catalog reference. Treat a missing parent differently from a duplicate identity in an API error contract; a generic successful upsert cannot repair every kind of constraint failure.

A preliminary lookup cannot replace the foreign key

A handler might first query whether a customer exists to produce a helpful error. That is useful feedback, but it is not a lasting promise. Under PostgreSQL’s default Read Committed isolation, an ordinary select sees the committed snapshot at the start of that command. Another transaction can remove an unreferenced customer afterward.

The two-connection fixture used customer 74. Connection A began a transaction and selected that customer, receiving one row. Connection B then deleted the customer and committed. Connection A tried to insert order 420 for customer 74. The foreign key rejected the insert with 23503; no orphan order appeared.

View full-size image ↗

Wrapping the preliminary select and insert in a transaction did not make this particular lookup reserve the parent. Other locking protocols can coordinate longer workflows, but they introduce their own ordering and contention requirements. For the relationship shown here, keep the database constraint and handle its possible failure at the final write.

Make deletion behavior visible

Deleting customer 73 is restricted because orders still reference it. Deleting order 418 is different: its line items are components of that order and use ON DELETE CASCADE. This cascade goes from the deleted order to its referencing lines. It does not delete the order’s customer.

Run this transaction to observe the cascade, then undo it:

BEGIN;
DELETE FROM orders WHERE id = 418 AND state = 'draft' RETURNING id;
SELECT count(*) AS remaining_lines FROM order_items WHERE order_id = 418;
ROLLBACK;
SELECT count(*) AS restored_lines FROM order_items WHERE order_id = 418;

The delete returns 418. The first count is zero because both lines disappeared within the transaction. After rollback, the second count is two. The order and its cascaded line deletions are part of the same database transaction, consistent with PostgreSQL’s transaction tutorial. Rollback restores these database rows; it does not undo an email or another external effect an application already emitted.

Choose a referential action from the relationship’s lifecycle. Cascading from a disposable draft to its lines is reasonable. Cascading from a customer to every historical order could erase more than the user intended. Inspect every downstream foreign key before enabling a cascade in a larger schema, because one delete can affect many tables and rows.

RESTRICT and the default NO ACTION are not interchangeable in every configuration. The latter can support a deferred constraint check when configured accordingly, while the restrictive delete action cannot be deferred. This example declares immediate constraints and does not demonstrate deferred ordering. SET NULL is another policy for optional relationships, but it would conflict with this mandatory customer column unless the model changed.

Referential integrity is not a paid-order policy

The state column restricts stored state names to draft or paid. It does not prohibit deleting a paid row. Compare the guarded delete with a direct delete, and roll back the latter experiment:

DELETE FROM orders WHERE id = 419 AND state = 'draft' RETURNING id;
SELECT count(*) AS paid_lines FROM order_items WHERE order_id = 419;
BEGIN;
DELETE FROM orders WHERE id = 419 RETURNING id;
SELECT count(*) AS paid_lines_after_direct_delete
FROM order_items WHERE order_id = 419;
ROLLBACK;

The first delete returns no row, and the paid-line count remains one. PostgreSQL’s DELETE reference documents that deleting zero rows is successful SQL execution. The application must inspect the result; an empty result is not proof that a requested cancellation happened.

The direct delete then returns 419 and its line count becomes zero. The foreign keys are entirely satisfied: removing the order and its lines leaves no broken reference. Rollback restores the example afterward. This is an intentional demonstration of a missing business restriction, not a recommended way to remove financial history.

If paid orders must be retained, constrain all permitted write paths with an appropriate database permission, controlled procedure or another explicit enforcement design. A state predicate in one handler does not bind a different writer with unrestricted delete privileges. Likewise, a foreign key does not prove that the caller owns the referenced customer. Authorization and retention rules need their own boundary.

Choose indexes without duplicating existing work

PostgreSQL creates a unique B-tree index for each primary key. It does not automatically create an index on every referencing column. Before the following statement, the small fixture had only the three primary-key indexes:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);
SELECT indexname FROM pg_indexes
WHERE schemaname = 'public' AND tablename IN ('customers', 'orders', 'order_items')
ORDER BY indexname;

Afterward the result includes orders_customer_id_idx as well. That index can support customer-to-order lookups and the search for referencing orders during parent changes. Its usefulness depends on access patterns and table size; this small example supplies no performance benchmark or universal index requirement.

The line table already has a primary-key index beginning with order_id. PostgreSQL’s multicolumn index guidance explains why leading-column conditions can use a B-tree index efficiently. Do not automatically add another index on the same leading column just because you added a foreign key. Examine existing indexes and actual plans first.

For an existing database, the migration also needs an inventory of orphan references, duplicate identities and null values, plus a plan for concurrent writers. This fresh-schema exercise does not establish a safe online migration procedure. Keep stable identity separate from mutable labels: the fixture changes customer 73’s display name while both order references remain 73.

As a final exercise, commit the deletion of the draft order and inspect all three tables. Only the paid order and its single line should remain, with their customer still present. Deleting that customer must still fail. The schema is doing useful work when each accepted operation, rejection and cascade matches a relationship you can explain, and when you can name the business rules it leaves unenforced.