Keys and Relationships

Why it matters

Keys are how an application finds its data again. Two customers with the same identifier send a support agent to the wrong account; an order that points at a deleted customer breaks the order history; a delete rule nobody asked for removes invoices together with their customer. The screen can look correct while the stored data is already wrong, and the failure surfaces later, in a report, an export or another service.

For a tester, every foreign key is a rule with a positive test (a valid reference is accepted) and negative tests (a missing parent is rejected; deleting a parent does what the requirement says).

Primary keys: one row, one identity

A primary key (PK) is a column, or a combination of columns, whose value is unique in every row and never NULL. A table has at most one, and the database rejects a row whose key is NULL or already used by another row. A primary key made of several columns is a composite key.

A natural key already means something in the real world, such as a product SKU. A surrogate key has no business meaning: an auto-incremented integer, or a UUID, a 128-bit identifier that an application can generate without asking the database for the next number. Teams often use a surrogate primary key and put a UNIQUE constraint, which rejects a value that another row already has, on the natural identifier, because real-world identifiers such as an email change.

• Uniqueness: a second customer with an existing email is rejected with a clear message, not a server error.
• Stability: a customer who changes their email keeps the same id, so their orders still point at them.
• Generation: keys do not collide when records are created in parallel, or after an import that wrote its own ids. Such an import can leave the database’s counter behind, so the next new record gets an id that is already taken.
• Exposure: sequential ids are easy to guess, so changing /orders/1043 in a URL to reach another customer’s order is an access-control test. A UUID is harder to guess but does not replace that check.

Try it: UUID Generator

Foreign keys and referential integrity

A foreign key (FK) is a column, or a set of columns, in a child table whose values must match the primary key, or another unique key, of a parent table. In orders.customer_id REFERENCES customers (id), orders is the child. Referential integrity holds when every non-null foreign key value matches an existing parent row; a child row that breaks it is an orphan.

With the foreign key declared, the database rejects an order for a customer that does not exist. Deleting a parent that still has children triggers the referential action written in the schema. Choosing it is a business decision, so confirm it with the product owner, then test it:

• NO ACTION (the default) or RESTRICT: the delete is rejected; the two differ only in when the check runs.
• CASCADE: the children are deleted with the parent.
• SET NULL: the children stay and their foreign key becomes NULL, so the column must allow NULL.
• A NULL foreign key passes the check unless the column is NOT NULL: right for an optional relationship, a defect for a mandatory one.
• Declared is not always enforced: by default SQLite leaves foreign keys unchecked unless each connection runs PRAGMA foreign_keys = ON, and no foreign key reaches into another service. There, only the application protects integrity.

One-to-one, one-to-many and many-to-many

Cardinality says how many rows on one side relate to how many on the other. All three kinds use the same two keys; only the position of the foreign key and the constraint on it change.

• One-to-many (1:N): one customer has many orders. The foreign key sits on the many side, orders.customer_id, and a parent may have no children at all.
• One-to-one (1:1): a customer has at most one loyalty card. The foreign key is also UNIQUE, so a second card for the same customer is rejected.
• Many-to-many (N:M): an order has many products and a product is in many orders. A junction table, order_items, stores one row per pair with a foreign key to each side; a composite primary key on the pair blocks duplicates.
• No foreign key can force a parent to have children: “every order has at least one line” is the application’s job, and an empty order is a classic defect.

Worked example: testing the keys of a small store

This schema is part of a store’s test basis: each REFERENCES clause is a relationship, and each ON DELETE clause, or its absence, is a rule. The product owner confirms that a customer with orders cannot be deleted, deleting an order removes its lines, and a product that was ordered cannot be deleted. Act through the UI or the API, then confirm the stored rows.

• Order for existing customer 42: accepted and stored with customer_id 42.
• Order for customer 999, which does not exist, sent through the API: rejected with a validation message, not a server error, and nothing stored.
• Delete customer 42, who has orders: rejected (RESTRICT).
• Second loyalty card for customer 42: rejected by the UNIQUE foreign key.
• Same product twice in one order: rejected by the composite key, or merged into one line if the requirement says so.
• Delete an order: its order_items rows go with it (CASCADE).
• Delete a product that was ordered: rejected (NO ACTION).
• Delete a customer who has a loyalty card but no orders: rejected too, because loyalty_cards has no delete rule (NO ACTION). Nobody stated this rule, so ask the product owner whether it is intended.

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  email       VARCHAR(254) NOT NULL UNIQUE,
  full_name   VARCHAR(100) NOT NULL
);

CREATE TABLE loyalty_cards (                 -- 1:1 with customers
  card_no     CHAR(10) PRIMARY KEY,
  customer_id INTEGER NOT NULL UNIQUE REFERENCES customers (id)
);

CREATE TABLE orders (                        -- 1:N: one customer, many orders
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  created_at  TIMESTAMP NOT NULL
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  sku         VARCHAR(20) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE order_items (                   -- N:M junction: orders x products
  order_id    INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id  INTEGER NOT NULL REFERENCES products (id),
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Try it: SQL Formatter Try it: SQL INSERT Generator

Frequently asked questions

References

• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.