Glossary

Primary key

A primary key establishes a record’s identity for the database and its relationships. It does not grant permission to view the record, nor is it automatically a public identifier.

Short definition

One stable identity for a row.

A primary key denotes the row’s main identity. It is useful for updates, deletions, foreign keys, and ORMs because the application knows exactly which record it is addressing. A table has one primary key, but that key can consist of multiple columns.

In PostgreSQL, a primary key enforces both uniqueness and NOT NULL and creates a unique B-tree index. That is PostgreSQL-specific behaviour; a general design should not assume that every database has identical implementation details.

The problem it solves

Unambiguous relationships and record changes

A primary key prevents the application from modifying “some similar” order instead of a specific row.

the identity of an order, customer, product, or import
the target of a foreign key from order items
mapping entity identity in an ORM
a stable internal reference for changes and auditing
distinguishing an internal ID, public UUID, and external marketplace ID

SQL example

PostgreSQL: internal and business order identities

The example uses a PostgreSQL identity column. The primary key is id, while separate rules protect the other unique values.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id uuid NOT NULL UNIQUE,
    marketplace_code text NOT NULL,
    external_order_id text NOT NULL,
    order_number text NOT NULL UNIQUE,
    UNIQUE (marketplace_code, external_order_id)
);

How it works

From creating an order to linking its data

Text alternative: a primary key links data; authorization still controls access to a specific order.

Creating the order The database generates or accepts a stable identity, such as a number or UUID.
A unique row The primary key prevents storing a second row with the same main identity or a NULL value.
Related items order_items.order_id can reference the order’s primary key through a foreign key.
Other identities An order number, public UUID, and external ID can be separate unique pieces of business data.
Authorization In addition to finding the ID, the server checks the user, tenant, and permission for the operation.

Important concepts

Identity can take several sensible forms.

The choice depends on the value’s stability, relationships, integrations, and how it is exposed.

Simple and composite key

A simple key uses one column. A composite key contains several columns, such as the pair order_id and product_id in a junction table.

Natural and surrogate key

A natural key exists in the domain, such as a stable code. A surrogate key is a technical identity. Neither is universally better.

Sequence and UUID

A sequential integer is straightforward to generate. A UUID can suit distributed creation or public URLs, but it does not provide authorization by itself.

Unique constraint

A table can have several uniqueness rules, but only one primary key. A unique business code therefore does not have to serve as the main identity.

ORM identity

An ORM works with entity identity, but does not replace the database constraint or the decision about which value is stable.

Benefits and limitations

The right identity is not a security boundary.

Benefits

unambiguous targeting of updates and deletions
a safe target for relationships between tables
clear documentation of the model’s main identity
support for ORMs and consistent imports

Common mistakes

claiming that a primary key must be an auto-incrementing number
using changing business data as an immutable identity
treating a UUID as authorization or data protection
exposing a sequential ID without considering information leakage
choosing a composite key without considering how it propagates to every relationship

Practical example

One order, four distinct identifiers

An order can have an internal primary key id, a public UUID public_id, an order_number for business communication, and the pair marketplace_code and external_order_id for idempotent imports. Each value serves a different purpose; there is no need to force them into the same value.

If the public UUID appears in a URL, the server must still verify whether the signed-in identity may view the order in the given tenant.

What to keep in mind

Choose an identity based on its stability and role.

Over time, a key propagates into relationships, migrations, APIs, and reports, so its meaning should be explicit.

distinguish technical identity, public reference, and integration business identifier
use UNIQUE for additional unique data
assess the effect of a composite key on every related foreign key
do not derive permission solely from the fact that the client knows an ID
consider access patterns and indexes when choosing how to generate an identity

Common questions

Primary identity in practice

Does a primary key have to auto-increment?

No. It can be a UUID, a stable natural value, or a combination of columns.

Can a table have multiple primary keys?

No. It has one primary key, although that key can contain multiple columns. UNIQUE constraints protect other unique values.

Is an order number a suitable primary key?

Only if it remains unchanged over the long term and meets the model’s needs. A separate technical key with the order number as a unique business value is often preferable.

Does a UUID prevent someone from reading another customer’s order?

No. An unpredictable identifier does not replace a server-side authorization check.

How I apply this principle in practice

I separate identities according to their real purpose.

In e-commerce and integrations, I design internal, public, and external identifiers together with database rules for safe changes and retries.

How I work with databases Discuss your project without obligation