Glossary

Foreign key

A column named customer_id is not a foreign key by itself. Only a database constraint ensures that the reference points to an existing row and defines what happens when that row changes.

Short definition

A valid reference between a parent and dependent record.

A foreign key checks whether the value in a column or combination of columns exists as a suitable identity in another table. An order item therefore cannot reference an order that does not exist. The rule also applies outside the main HTTP form, such as during imports or concurrent writes.

A foreign key is neither an ORM association nor authorization. Doctrine can map the relationship to objects, but the database protects referential integrity. The application must separately verify on the server whether a particular user may work with the order.

The problem it solves

Relationships that must always point to a valid record

A foreign key expresses a reference and, where applicable, the dependency of internal data in a relational model.

an order references a customer
an order item references an order and a product
a junction table connects a product and a category
a hierarchical table references itself through parent_id
deletions and changes follow a deliberately chosen referential policy

SQL example

PostgreSQL: order referential integrity

The product_id index is explicit here to support queries and product deletion checks; its need should be verified against real workloads.

CREATE TABLE orders (
    id uuid PRIMARY KEY,
    customer_id uuid NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
    order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity integer NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX order_items_product_id_idx ON order_items(product_id);

How it works

From a parent to a valid order item

Text alternative: an item is valid only if its order_id points to an existing order under the chosen policy.

Parent identity The orders table has a primary or another suitable unique key.
Referencing column order_items.order_id holds the value intended to reference a row in orders.
Database check On insertion and update, the database verifies that the parent row exists.
ON DELETE and ON UPDATE When the parent changes, the selected policy applies: RESTRICT, CASCADE, SET NULL, or another supported option.
Committing a valid state The transaction commits the relationship only in a form that satisfies the integrity rule.

Important concepts

A reference, index, and association are not synonyms.

The specific action taken on deletion is a domain decision, not a technical default.

Parent and dependent row

The referenced, or parent, row is the target. The referencing, or dependent, row contains the foreign key value.

Required and optional relationship

NULL in a foreign key can represent an optional relationship. If the parent must exist, NOT NULL is added. NULL is not a reference to a nonexistent row.

ON DELETE and ON UPDATE

RESTRICT or NO ACTION rejects the change. CASCADE deletes dependent records, while SET NULL removes the relationship. The right choice depends on the meaning of the data.

Many-to-many

A junction table with two foreign keys holds the relationship between products and categories; the same rule applies to other many-to-many relationships.

Foreign key and index

PostgreSQL does not automatically create an index on the referencing side of a foreign key. The necessary index is designed around JOINs and parent deletions; other databases may differ.

Benefits and limitations

Referential integrity leaves less room for inconsistent data.

Benefits

protecting valid references even during concurrent writes
clear documentation of the relationship between tables
deliberate behaviour when a parent record is deleted
more reliable data for JOINs, reports, and imports

Common mistakes

treating customer_id without a constraint as a protected relationship
assuming a foreign key always creates a suitable index automatically
using ON DELETE CASCADE mechanically
confusing a foreign key with an ORM association or authorization
trying to enforce the same relationship across the boundary of an external system

Practical example

Order, item, and product

orders.customer_id references customers.id. order_items.order_id references orders.id, and product_id references products.id. If an item is genuinely part of an order, CASCADE can make sense when that order is deleted; by contrast, a historical product is often retained so its relationship to completed orders is not lost.

A foreign key does not grant permission to delete or read data in another organisation. Tenant context and permissions are separate business and security rules.

What to keep in mind

Confirm the business meaning of a dependency before deleting data.

A constraint protects the data, but the application design still determines the deletion process.

use NOT NULL when the relationship is not optional
use CASCADE only for truly dependent data
verify indexes on the referencing side against queries and data volume
do not treat an external service reference as protected merely because its column is named like a foreign key
turn a constraint violation into a clear application error

Common questions

Foreign keys in practice

Does a foreign key always create an index?

No. PostgreSQL indexes the target of a primary or unique key, but does not automatically create a suitable index on the referencing foreign key columns. Other systems have different implementation details.

Should every customer_id be a foreign key?

Often yes for internal tables containing owned data. For an external ID, the source of truth may live in another service where the local database cannot enforce a foreign key.

When should I use CASCADE?

When the dependent record makes no sense without its parent and cascading deletion is deliberately intended. It is not a safe default for historical data.

Does a foreign key replace application validation?

No. The application can explain the error and controls the business process; the database also protects the invariant during concurrency and through other write paths.

How I apply this principle in practice

I protect relationships between data beyond the application form.

In e-commerce models, I design relationships among orders, items, products, and integrations together with their effects on deletion, imports, and performance.

How I work with databases Discuss your project without obligation