Composite Primary Key SQL: A Practical Guide

Your custom AI platform has a workflow assignment table. One model version can serve many teams, and each team can use many model versions. A single generated id would identify rows, but it wouldn't express the rule that the same team-model assignment must not appear twice.

The same problem shows up in multi-tenant workflow records, order lines, membership tables, and automation steps. A row often becomes unique only within a combination of dimensions. That's where composite primary key SQL becomes more than a syntax exercise. The key affects foreign keys, indexes, ORM models, API payloads, cache keys, URLs, and future migrations.

Table of Contents

• Why Composite Keys Matter in Real Systems
• What a Composite Primary Key Actually Is
  • Where the model fits
• Writing the Syntax Across PostgreSQL, MySQL, and SQL Server
  • The behavior that changes by engine
• How Column Order Controls Index Performance
  • Match the key to the workload
• Referencing Composite Keys with Foreign Keys
  • Actions and portability
• When to Choose Composite Natural Keys vs Surrogate Keys
  • The decision test
• Altering Composite Keys and Migrating Without Downtime
  • A safer migration sequence
• Operational Best Practices and Pitfalls to Avoid

Why Composite Keys Matter in Real Systems

A team adds a uniqueness check to the application layer:

Does this team already use this model version?

Then two workers process assignment requests at nearly the same time. Both read “no,” both insert, and the database ends up with duplicate relationship rows. The application tried to protect the rule, but the rule belonged in the schema.

A junction table is often the clearest example:

CREATE TABLE team_model_assignments (
    team_id         BIGINT NOT NULL,
    model_version_id BIGINT NOT NULL,
    assigned_at     TIMESTAMP NOT NULL,
    CONSTRAINT team_model_assignments_pk
        PRIMARY KEY (team_id, model_version_id)
);

The pair identifies the assignment. team_id can repeat because one team may use many model versions. model_version_id can repeat because one model version may serve many teams. The duplicate pair is what the database rejects.

Practical rule: If the business rule says “this relationship may exist once for this combination,” enforce that combination in the database.

A composite key can also make tenant scope explicit. Suppose an internal automation system stores workflows with (tenant_id, workflow_id). A workflow identifier may be unique only inside its tenant. Making both columns part of the key prevents a child record from accidentally connecting to a workflow belonging to another tenant.

The important decision happens after the PRIMARY KEY declaration. Every table that references the row must carry the complete key, every ORM model must represent multiple identity fields, and every integration must transmit enough information to locate the record. The key's column order also shapes the automatically created index, so it influences query behavior.

This is why I treat a composite primary key as a lifecycle contract. It describes how the record is identified today, how other systems will refer to it tomorrow, and how expensive a future identity change will become.

What a Composite Primary Key Actually Is

Think of a building address that needs both a building number and a floor. Building number alone may identify the property, but not the apartment or office. Floor alone is even less useful. Together, the two values identify the location.

A composite primary key is a primary-key constraint formed from two or more columns whose combined values uniquely identify each row. SQL treats the combination as one identifier. It doesn't require each individual column to be unique.

An order-line table makes the rule concrete:

CREATE TABLE order_lines (
    order_id    BIGINT NOT NULL,
    line_number INTEGER NOT NULL,
    product_id  BIGINT NOT NULL,
    quantity    INTEGER NOT NULL,
    CONSTRAINT order_lines_pk
        PRIMARY KEY (order_id, line_number)
);

order_id repeats because one order contains multiple lines. line_number repeats because many orders can have a line numbered the same way. The pair (order_id, line_number) must be unique. A second row with the same pair is rejected, while repeated values in either column remain valid when the other value differs.

Every component must be non-null. PostgreSQL describes a primary key as the equivalent of applying both UNIQUE and NOT NULL, while also recording schema metadata that tells other tables which columns identify the row. PostgreSQL also permits only one primary-key constraint per table, but that one constraint can contain multiple columns. See the PostgreSQL table-creation documentation for the formal constraint behavior.

Where the model fits

This pattern is natural for:

• Many-to-many junctions: (team_id, model_version_id) prevents duplicate assignments.
• Order lines: (order_id, line_number) identifies a line within its order.
• Scoped identifiers: (tenant_id, external_id) identifies a record within a tenant.
• Relationship records: (workflow_id, step_id) identifies a step belonging to a workflow.

The key question is whether the combination is the actual identity of the row, rather than merely a convenient way to prevent duplicates. If it is the true identity, a composite primary key can express the model directly. If it is only an alternate uniqueness rule, a single primary key plus a separate composite UNIQUE constraint may create less coupling.

Writing the Syntax Across PostgreSQL, MySQL, and SQL Server

The declaration is broadly portable. The operational behavior around the declaration differs, so use explicit constraint names and verify the engine-specific index behavior before committing to a design.

Here is the same product-vendor relationship in PostgreSQL:

CREATE TABLE product_vendors (
    product_id BIGINT NOT NULL,
    vendor_id  BIGINT NOT NULL,
    price      NUMERIC NOT NULL,
    CONSTRAINT product_vendors_pk
        PRIMARY KEY (product_id, vendor_id)
);

MySQL with InnoDB uses the same table-constraint form:

CREATE TABLE product_vendors (
    product_id BIGINT NOT NULL,
    vendor_id  BIGINT NOT NULL,
    price      DECIMAL(12, 2) NOT NULL,
    CONSTRAINT product_vendors_pk
        PRIMARY KEY (product_id, vendor_id)
) ENGINE = InnoDB;

SQL Server follows the same logical syntax:

CREATE TABLE product_vendors (
    product_id BIGINT NOT NULL,
    vendor_id  BIGINT NOT NULL,
    price      DECIMAL(12, 2) NOT NULL,
    CONSTRAINT product_vendors_pk
        PRIMARY KEY (product_id, vendor_id)
);

You can also declare a primary key inline when the definition is simple, but the table-constraint form is clearer for composite keys and makes naming explicit. The pair allows the same product to appear for multiple vendors and the same vendor to sell multiple products. It rejects only a duplicate pair, as described in Microsoft's primary and foreign key documentation.

The behavior that changes by engine

PostgreSQL automatically creates a unique B-tree index for a primary key. MySQL with InnoDB uses the primary key as the table's clustered index, so the key affects how the table is organized. SQL Server automatically creates a unique index and uses a clustered index for the primary key by default unless the definition or existing table design says otherwise.

Those defaults matter when the key is wide, when rows are frequently updated, or when another index already owns the clustered role. For a broader comparison of enterprise database behavior, the enterprise database comparison for developers provides useful context.

Behavior	PostgreSQL	MySQL (InnoDB)	SQL Server
Composite declaration	PRIMARY KEY (a, b)	PRIMARY KEY (a, b)	PRIMARY KEY (a, b)
Primary-key index	Unique B-tree	Clustered primary-key index	Unique index, clustered by default
Key columns	Non-null	Non-null	Non-null
Partial foreign-key reference	Invalid unless separately unique	Depends on engine and declared referenced key	Requires a matching referenced key
Main design concern	Index order and key width	Clustered row organization	Clustered choice and index width

A child table generally cannot reference only product_id from this key because product_id alone isn't guaranteed to identify one parent row. It must reference the complete pair, unless product_id has its own unique constraint.

How Column Order Controls Index Performance

A composite primary key creates a physical access path, not just a logical rule. PostgreSQL's documentation states that adding a primary key automatically creates a unique B-tree index on the declared column or group of columns. The index is ordered by the columns in the order you declare them.

Consider:

PRIMARY KEY (tenant_id, record_id)

The index groups entries by tenant_id, then orders each tenant's entries by record_id. A query filtering on both values has a direct path. A query beginning with tenant_id can commonly use the leading part of the index. A query filtering only on record_id doesn't get an equivalent index beginning with record_id, because the database must work through the tenant groups first.

Match the key to the workload

In a multi-tenant operations platform, tenant-scoped dashboards usually ask for records within one tenant. That makes (tenant_id, record_id) a sensible order when the application routinely filters by tenant and then narrows to a local identifier.

An audit export might behave differently. If it searches by record_id across tenants, the primary-key order won't provide the most direct access path. Add a separate index only if that workload is important and representative. Don't duplicate indexes automatically. Each additional index consumes storage and increases write and maintenance work.

The right choice comes from actual queries, not intuition. Review filters, joins, sort requirements, and cardinality. Then validate representative statements with EXPLAIN or the engine's equivalent using production-like data distribution. The Arch's database optimization guide is a useful companion when you're evaluating indexes as part of a wider database design.

For AI and automation systems, this matters in tables such as workflow runs, feature assignments, and model evaluation records. A dashboard may always scope by tenant, while a worker may retrieve a record by an external identifier alone. Those are different access paths, and one composite primary-key index may not serve both efficiently. See how this kind of workload can appear in a real-time lead-scoring system.

Index-order rule: Put the consistently filtered partitioning or tenant column first when most access is tenant-scoped. Put the local identifier or range column after it, then add a reverse-direction index only for a proven workload.

Referencing Composite Keys with Foreign Keys

The moment another table points to a composite primary key, the key becomes an integration contract. The child must carry the complete parent identity, not whichever component happens to be convenient.

A multi-tenant workflow schema might look like this:

CREATE TABLE workflows (
    tenant_id   BIGINT NOT NULL,
    workflow_id BIGINT NOT NULL,
    name        TEXT NOT NULL,
    CONSTRAINT workflows_pk
        PRIMARY KEY (tenant_id, workflow_id)
);

CREATE TABLE automation_steps (
    tenant_id   BIGINT NOT NULL,
    workflow_id BIGINT NOT NULL,
    step_id     BIGINT NOT NULL,
    action_type TEXT NOT NULL,
    CONSTRAINT automation_steps_pk
        PRIMARY KEY (tenant_id, workflow_id, step_id),
    CONSTRAINT automation_steps_workflow_fk
        FOREIGN KEY (tenant_id, workflow_id)
        REFERENCES workflows (tenant_id, workflow_id)
);

The child preserves tenant scope and workflow identity together. A workflow_id that exists in another tenant cannot satisfy the relationship. The referenced and referencing columns must align in order, data type, and collation. That alignment is easy to miss when separate teams own the parent and child migrations.

Actions and portability

ON DELETE and ON UPDATE actions become more consequential when every relationship carries multiple columns. SET NULL requires the foreign-key columns to be nullable, and setting part of an operational identity to null usually makes the relationship harder to reason about. For stable workflow identifiers, RESTRICT or an archival strategy is generally safer than nulling the reference.

Here's the practical portability issue. MySQL documents that InnoDB permits foreign keys to reference non-unique keys as an extension, while NDB requires an explicit unique or primary key. That behavior isn't a safe assumption for a system that may move between engines or environments. Reference a declared composite PRIMARY KEY or UNIQUE constraint and test the actual production engine.

The child model now affects more than SQL. An ORM must represent a compound identity, serializers must include both values, and event payloads must preserve their order and names. A task event containing only workflow_id is incomplete when workflow identity is tenant-scoped.

The video below provides another visual treatment of the parent-child relationship:

When to Choose Composite Natural Keys vs Surrogate Keys

Composite natural keys are usually the right choice for junction data. A membership, order item, or team-model assignment already has a relationship identity. Adding an artificial identifier can hide the rule that prevents duplicate relationships.

They're usually not my first choice for general entity tables. A workflow, customer, document, or model version tends to be referenced by many systems, and those systems benefit from one stable identifier. In that situation, use a surrogate primary key and preserve the business rule separately:

CREATE TABLE workflow_assignments (
    id              BIGINT NOT NULL,
    tenant_id       BIGINT NOT NULL,
    workflow_id     BIGINT NOT NULL,
    model_version_id BIGINT NOT NULL,
    CONSTRAINT workflow_assignments_pk
        PRIMARY KEY (id),
    CONSTRAINT workflow_assignments_scope_uq
        UNIQUE (tenant_id, workflow_id, model_version_id)
);

The composite UNIQUE constraint still prevents duplicate assignments. The surrogate id gives child tables and integrations one compact reference.

The decision test

Choose a composite natural key when all of the following are true:

• Identity is genuine: The column combination describes what the row is, not just how the current application happens to find it.
• Values are stable: Components won't be edited as ordinary business data.
• Values are universal: Every system that touches the row can provide the complete combination.
• Relationships dominate: The table represents an association, scoped record, or line item.

Choose a surrogate key when one component may change, when consumers often identify the row using only one component, or when carrying the full key through many dependent tables would create unnecessary coupling. Changing one part of a natural key can require cascading updates to foreign keys, cache keys, URLs, and integration references.

A hybrid design is often the most maintainable choice for custom AI tooling. Use a surrogate identity for a broadly referenced entity, then add a composite uniqueness constraint for the business rule. This keeps duplicate prevention at the database boundary without forcing every consumer to understand every business dimension. Teams evaluating whether to build or buy adjacent AI capabilities can also use this build-vs-buy perspective for AI tooling.

Altering Composite Keys and Migrating Without Downtime

Adding a composite primary key to an existing table is straightforward only when the data already satisfies the rule:

ALTER TABLE workflow_runs
ADD CONSTRAINT workflow_runs_pk
PRIMARY KEY (workflow_id, run_id);

Existing duplicate pairs or null components will cause the constraint to fail. Clean and validate the data before changing the schema. For a hot table, perform duplicate detection and remediation in controlled batches, then schedule the constraint operation around the engine's locking behavior.

Changing the key composition is more disruptive. You may need to add a column, backfill it, update every child foreign key, drop the old constraint, and add the new one. PostgreSQL doesn't redefine a primary key in place. The practical pattern is to drop and re-add the constraint, often within a transaction, while keeping lock windows as short as possible:

BEGIN;

ALTER TABLE workflow_runs
    DROP CONSTRAINT workflow_runs_pk;

ALTER TABLE workflow_runs
    ADD CONSTRAINT workflow_runs_pk
    PRIMARY KEY (tenant_id, workflow_id, run_id);

COMMIT;

That statement is not a universal zero-downtime migration. Dependent foreign keys and concurrent writes must be handled deliberately.

A safer migration sequence

1. Add the new column as nullable if necessary.
2. Backfill it with deterministic values and validate missing rows.
3. Create supporting uniqueness using the engine's online options where available. PostgreSQL supports CREATE UNIQUE INDEX CONCURRENTLY; MySQL can use ALGORITHM=INPLACE, LOCK=NONE where supported; SQL Server migrations can use small-batch validation and carefully planned index operations.
4. Add or update child columns before removing the old reference.
5. Deploy application code that can read and write both shapes during the transition.
6. Switch foreign keys and primary-key constraints during a controlled lock window.
7. Remove compatibility columns and indexes only after dependent services have migrated.

The same discipline applies when evolving an AI workflow schema. If a client portfolio agent depends on workflow identity, changing the key without coordinating its consumers can strand events and cached records. Treat the migration as an API version change, not a local table edit. A related example of this kind of operational system is a client portfolio agent.

Operational Best Practices and Pitfalls to Avoid

Before production, review the key against the way the system runs:

• Enforce uniqueness in the database: Application-side checks can race under concurrent requests. The constraint must own the rule.
• Keep every component required: Primary-key columns are non-null. Put optional metadata, such as a reviewer identifier, in a separate nullable column.
• Keep the identifier compact: Every referencing foreign key carries the complete key, so unnecessary columns increase row size, join work, and index width.
• Test column order: Use EXPLAIN against production-like cardinalities. A logically correct key can still be the wrong access path.
• Protect immutable references: Prefer RESTRICT or archival behavior over SET NULL when nulling the relationship would hide an operational problem.
• Plan the consumers: Check ORM identity handling, event schemas, cache keys, URLs, and external payloads before approving the migration.

The design note I'd want in a review is simple:

The composite key is the row's stable business identity, every component is required, its order matches the dominant access path, and all dependent systems can carry the complete key.

If that sentence isn't true, use a surrogate primary key with an explicit composite UNIQUE constraint instead.

---

Internal Systems designs and builds custom software, integrated internal tools, and AI-enabled workflows for operational teams. If your multi-tenant automation or AI platform needs a durable data model, migration plan, or reliable workflow architecture, visit Internal Systems to discuss the system you need to ship.