Composite Constraints in SQL

Introduction

A composite constraint is one database rule defined across two or more columns. The columns are evaluated together as one key, relationship, or condition rather than as independent rules.

CONSTRAINT uq_enrollment
UNIQUE (student_id, course_id)

This does not require each student or course to appear only once. It requires each student-course pair to be distinct. Composite constraints translate real-world scope into enforceable SQL.

Why Composite Constraints Exist

Many business facts are identified only by a combination. A student can take many courses, a course has many students, a warehouse stores many products, and a product appears in many warehouses.

Single-column uniqueness would reject valid rows. Leaving the combination unconstrained would allow duplicate facts. A composite rule captures the exact boundary.

Composite Means One Multi-Column Rule

UNIQUE (tenant_id, email) means an email can repeat across tenants but not inside one tenant. It is not equivalent to making both columns individually UNIQUE.

The same principle applies to composite primary keys, foreign keys, and checks: the complete set expresses one logical rule.

Start With Business Grain

Grain describes what one row represents. An enrollment row may represent one student in one course for one semester. If semester is omitted from the key, valid repeat enrollment may be blocked or historical rows may collapse into one fact.

State the grain in plain language before choosing columns. Wrong grain produces wrong constraints even when the SQL is valid.

Composite PRIMARY KEY

A composite primary key uses multiple non-null columns as the table's principal row identity.

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    CONSTRAINT pk_enrollments
      PRIMARY KEY (student_id, course_id)
);

The table has one primary-key constraint containing two components. Neither component is individually unique.

Order-Line Identity

An order line can be identified by order plus line number. Line 1 repeats in every order, so line_number alone is not a key.

CONSTRAINT pk_order_items
PRIMARY KEY (order_id, line_number)

This expresses that line numbers are scoped by order. Scope is central to composite design.

Composite UNIQUE

A composite UNIQUE constraint protects an alternate business key while permitting repetition of individual components.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    seller_id BIGINT NOT NULL,
    sku VARCHAR(50) NOT NULL,
    CONSTRAINT uq_seller_sku UNIQUE (seller_id, sku)
);

The same SKU may be used by different sellers, but one seller cannot reuse it for two products.

Tenant-Scoped Uniqueness

Multi-tenant systems commonly require names, usernames, emails, or codes to be unique within a tenant rather than globally.

CONSTRAINT uq_tenant_email
UNIQUE (tenant_id, email)

Omitting tenant scope rejects valid values across customers. Omitting email from the constraint fails to protect the business key.

Three-Column Uniqueness

Some facts need more scope, such as one room number within one building on one campus, or one seat on one flight occurrence.

Add every attribute required to distinguish the grain, but no irrelevant attributes. Extra columns weaken uniqueness because duplicate facts can differ only in the unnecessary component.

Surrogate Primary Key Plus Composite UNIQUE

A generated ID can simplify references while a composite UNIQUE preserves business meaning.

CREATE TABLE inventory (
    inventory_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    CONSTRAINT uq_inventory_location
      UNIQUE (warehouse_id, product_id)
);

The surrogate does not eliminate the natural business rule. Both constraints serve different purposes.

Composite PRIMARY KEY or Surrogate Key?

Composite primary keys communicate natural grain and prevent duplicate junctions directly. Surrogates produce narrower foreign keys and can simplify ORMs, APIs, and references from many child tables.

Choose based on stability, width, propagation, and domain usage. If a surrogate becomes primary, preserve the candidate combination with UNIQUE.

Composite FOREIGN KEY

A composite foreign key references one composite candidate key in a parent table.

CREATE TABLE warehouses (
    company_id BIGINT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    PRIMARY KEY (company_id, warehouse_code)
);

CREATE TABLE shipments (
    shipment_id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    warehouse_code VARCHAR(20) NOT NULL,
    CONSTRAINT fk_shipment_warehouse
      FOREIGN KEY (company_id, warehouse_code)
      REFERENCES warehouses(company_id, warehouse_code)
);

The pair must match one parent. Matching each value to unrelated parent rows is not sufficient.

Column Order and Mapping Matter

Child columns must map to the corresponding parent columns in the declared order. Reversing equally typed columns may create a relationship that compiles but represents the wrong meaning.

Use consistent names, compatible types, and explicit constraint names. Review the mapping as a tuple, not as separate fields.

Tenant-Safe Relationships

Including tenant ID in both parent uniqueness and child foreign keys can prevent one tenant's row from referencing another tenant's local identifier.

FOREIGN KEY (tenant_id, project_id)
REFERENCES projects(tenant_id, project_id)

This makes tenant ownership part of referential integrity rather than relying solely on application filters.

Composite CHECK Constraints

A CHECK can compare multiple columns in the same row.

CONSTRAINT chk_booking_dates
CHECK (end_date > start_date)

Other examples include minimum price not exceeding maximum price, approved status requiring an approver, or shipped quantity not exceeding ordered quantity.

Conditional Multi-Column Rules

CONSTRAINT chk_approval_state
CHECK (
  status <> 'APPROVED'
  OR (approved_by IS NOT NULL AND approved_at IS NOT NULL)
)

Write a truth table before encoding complex conditions. Keep parentheses explicit so the rule remains reviewable.

NULL Semantics in Composite CHECK

SQL expressions can be true, false, or unknown. A CHECK rejects false but commonly accepts unknown, so NULL components may allow a row unless NOT NULL or explicit NULL logic closes that path.

State whether all components are required, all may be absent, or specific partial combinations are permitted.

NULL Semantics in Composite UNIQUE

Databases differ in how UNIQUE constraints treat NULL values and whether several rows with partially NULL combinations are allowed. Do not assume identical behavior across engines.

Use NOT NULL when the candidate key must be complete. When NULL is legitimate, test the exact production DBMS and document the intended uniqueness behavior.

Partial NULL Composite Foreign Keys

A partly NULL foreign key can behave unexpectedly under ordinary matching rules. If the relationship is optional only as a whole, add NOT NULL to all components or a CHECK requiring either all values present or all absent.

DBMS support for advanced match options varies. Explicit modeling is safer than relying on assumptions.

Composite Constraint Versus Composite Index

A constraint states an integrity rule. An index is a physical access structure. Databases often use a composite unique index to enforce a composite UNIQUE or PRIMARY KEY, but their semantic roles remain different.

Create constraints to communicate and enforce business rules. Design additional indexes for query and join performance.

Index Column Order

An index on (tenant_id, email) supports leading-tenant access differently from (email, tenant_id). Both combinations express similar uniqueness, but query performance can differ.

Choose constraint order consistently and evaluate workload access paths. Add a separate index when integrity order and query order need different structures.

Join on the Complete Relationship

Joining only part of a composite key can mix rows from different tenants, versions, warehouses, or time periods.

SELECT t.task_id, p.project_name
FROM tasks t
JOIN projects p
  ON p.tenant_id = t.tenant_id
 AND p.project_id = t.project_id;

The join predicate should mirror the full relationship unless a different, intentional relation is being queried.

Never Concatenate Columns to Fake a Key

String concatenation introduces delimiter collisions, formatting ambiguity, collation issues, conversion overhead, and lost type information. Values (1, 23) and (12, 3) can collide under naive concatenation.

Keep attributes atomic and declare the actual multi-column constraint. The database can compare typed components correctly.

Data Type Compatibility

Each corresponding parent and child component should use compatible type, length, precision, signedness, character set, and collation. A mismatch can block constraint creation or produce inefficient joins.

Use shared domain definitions or migration helpers so a composite relationship remains aligned across tables.

Composite Keys and Stability

Primary-key components should be stable. If tenant code, username, or business category changes, every child foreign key may need cascading updates.

A surrogate primary key can isolate mutable business identity while a composite UNIQUE tracks the current business rule. That is often safer for widely referenced entities.

Many-to-Many Junction Tables

Junction tables naturally use composite constraints. A student-course pair, user-role pair, or order-product pair can be the primary key when each relationship occurs once.

If the relationship has quantity, status, timestamps, or history, confirm whether those attributes change the grain. Repeated relationships may require an occurrence ID or an additional key component.

Grain Can Change Over Time

A key of (student_id, course_id) becomes too narrow when students may retake courses in different semesters. Add semester or introduce a separate enrollment identity plus an appropriate business UNIQUE rule.

Changing a composite constraint is a data-model migration, not merely a syntax edit.

Effective-Dated Data

Salary history might use (employee_id, effective_from) to prevent two versions beginning on the same date. This does not prevent date ranges from overlapping.

Overlap is a cross-row temporal rule that may require exclusion constraints, specialized temporal features, serialized application logic, or triggers. Composite uniqueness cannot express every domain rule.

Case, Collation, and Canonical Values

Tenant-scoped usernames or emails may need case-insensitive uniqueness. Database collation and normalization determine whether differently cased or spaced strings compare as equal.

Choose canonical representation, collation, and indexes deliberately. Application lowercase conversion alone does not protect every writer.

Composite Constraints and ORMs

ORMs may require embedded ID objects, multiple key mappings, or special equality implementations for composite identities. Surrogates can simplify object identity, but database constraints should still preserve business rules.

Inspect generated joins and DDL. An ORM mapping is not a substitute for database integrity.

Composite Constraints and APIs

Composite identities can appear in nested routes such as tenant and project IDs. Ensure authorization and queries use every scope component. A project ID valid in one tenant must not resolve inside another.

A surrogate public resource ID can simplify routes, but tenant ownership still requires explicit validation.

Soft Deletes Change Uniqueness

A simple UNIQUE (tenant_id, email) prevents reuse even after a row is soft deleted. Adding deleted_at to the combination may still behave unexpectedly because of NULL semantics.

Filtered or partial unique indexes, active-row tables, archival moves, or another DBMS-specific design may better express uniqueness among active rows. Soft-delete policy belongs in the constraint design.

Partitioning and Sharding

Some databases require partition columns in unique or primary keys. Composite tenant or shard keys can align integrity with physical placement and allow local uniqueness.

Global uniqueness across shards may need centralized allocation, distributed consensus, globally unique IDs, or reconciliation. A locally enforced composite constraint cannot protect data outside its authority.

Constraint Width and Storage Cost

Wide composite primary keys propagate into child foreign keys and secondary indexes, multiplying storage, memory, I/O, and network cost. Long strings make this effect larger.

Do not shorten real business data merely to fit an index. Consider a narrow surrogate for propagation while retaining a composite UNIQUE candidate key.

Avoid Redundant Constraints

If email is individually UNIQUE, then (tenant_id, email) adds no new protection because email is already globally unique. Conversely, composite UNIQUE does not imply either component is individually unique.

Review implication carefully. Redundant constraints consume storage and write work without improving integrity.

Composite Constraints and Concurrency

An application that checks for a duplicate and then inserts can race with another transaction doing the same. A database composite UNIQUE constraint resolves the final state atomically.

Application validation remains useful for friendly messages, while the constraint is the authoritative protection for every concurrent writer.

Adding Constraints to Existing Data

Profile existing combinations before adding PRIMARY KEY or UNIQUE. Find duplicates, NULL components, inconsistent scope, and rows suggesting that the proposed grain is wrong.

SELECT tenant_id, email, COUNT(*) AS duplicate_count
FROM users
GROUP BY tenant_id, email
HAVING COUNT(*) > 1;

Investigate duplicates before deleting or merging them. Data problems often reveal missing business rules.

Finding Composite Foreign-Key Orphans

Join child to parent on every component and find rows with no complete match. A partial join can incorrectly report a parent from the wrong tenant or scope.

Repair source mappings, create legitimate parents, correct children, archive invalid history, or redesign the model according to business meaning.

Large-Table Migration Cost

Building a composite unique index or validating a foreign key can scan data, sort keys, consume temporary storage, acquire locks, generate logs, and delay replication.

Estimate key width, row count, duplicate cleanup, online-build options, and rollback behavior. A short DDL statement can be a substantial production operation.

Updating Composite Keys

Changing any key component can update every referencing child and related index. Cascades may be correct but expensive, while restrictive actions force deliberate migration.

Prefer stable identities for heavily referenced rows. When a business key changes, a surrogate can preserve relationships while the alternate composite UNIQUE value is updated.

Constraint Names as Documentation

Use names such as pk_order_items, uq_tenant_email, fk_task_project_scope, and chk_booking_dates. Clear names improve errors, migrations, monitoring, and code review.

Document the plain-language grain and scope alongside the enforced SQL. Comments alone can drift; constraints make the rule executable.

The Referenced Columns Must Be a Candidate Key

A foreign key must identify at most one parent row. Therefore, its referenced column set normally has to be a parent PRIMARY KEY or UNIQUE candidate key. An ordinary non-unique index is not enough merely because it makes lookup fast.

For example, a child cannot safely reference (country_code, city_name) if the parent allows several cities with the same pair. Add the missing scope, establish parent uniqueness, or reference another stable key. Referential integrity depends on parent identity, not just index availability.

Building and Apartment Example

An apartment number such as 204 is usually unique only inside one building. A natural rule is UNIQUE (building_id, apartment_number). Residents can then reference the apartment's surrogate ID, or a child can carry both components if building scope is important to the relationship.

Making apartment number globally unique would reject valid buildings. Constraining only building ID would permit duplicate apartment numbers inside one building. The composite rule matches the domain sentence: one apartment number per building.

Flight Seat Example

A seat such as 12A repeats on many flights. Even a flight number repeats each day, so (flight_number, seat_number) may still be too narrow. The real key could be (flight_instance_id, seat_number), where the flight instance identifies one scheduled departure.

This example shows why familiar labels are not necessarily stable identities. Date, route, aircraft, and rescheduling rules should be considered before selecting a candidate key.

Composite Constraints Support Normalization

Normalized tables separate entities and relationships while constraints reconnect them accurately. A course enrollment table should reference students and course offerings, then protect one enrollment for the chosen grain. A junction should not repeat student names or course descriptions merely to detect duplicates.

Composite keys are therefore not a workaround for normalization. They often express the natural identity of an associative relation created by normalization.

Two Individual UNIQUE Rules Are Different

UNIQUE (student_id),
UNIQUE (course_id)

These rules allow each student and each course only once, effectively modeling a one-to-one relation. By contrast, UNIQUE (student_id, course_id) allows each student and course to appear many times, but never as the same pair twice.

This distinction is a common design and interview question. Parentheses group the columns into one candidate key.

Composite Checks Cannot Read Other Rows

A CHECK constraint ordinarily evaluates the current row. It can compare start and end values but cannot reliably assert that no other booking overlaps, that a running total stays below a limit, or that a sequence has no gap.

Cross-row rules may require a UNIQUE or exclusion constraint, a parent aggregate protected by transactions, a trigger, or serialized service logic. Select the database feature that matches the rule's scope instead of forcing every rule into CHECK syntax.

Upserts Need the Correct Conflict Target

Insert-or-update operations often use a composite candidate key as their conflict target. Inventory synchronization may upsert on (warehouse_id, product_id), while tenant user import may target (tenant_id, external_user_id).

If an upsert targets only part of the combination, it can update the wrong row or fail because that partial set is not unique. Keep ingestion, merge, and retry logic aligned with the declared candidate key.

Constraint Enforcement and Error Handling

Applications should treat a constraint violation as an expected integrity outcome, not parse a vendor's human-readable error message. Map named constraints or database error codes to stable domain responses, such as duplicate tenant email or invalid warehouse reference.

Do not expose internal schema details to clients. Log enough structured context to diagnose the combination while redacting sensitive values.

Immediate and Deferred Validation

Some databases can defer selected constraints until transaction commit. This can help when a transaction temporarily breaks a relationship while reordering or swapping composite keys but restores consistency before commit.

Deferral is DBMS-specific and should be used intentionally. It does not permit an invalid committed state. Immediate enforcement remains easier to reason about for ordinary inserts and updates.

Database Portability

The core concepts are portable, but syntax and behavior vary among PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. Differences include NULL uniqueness, filtered indexes, generated identity syntax, constraint deferral, index limits, online validation, and foreign-key requirements.

Write migrations for the actual supported engines. A generic SQL example explains intent, while production DDL must reflect vendor capabilities and tested behavior.

Worked Multi-Tenant Project Model

CREATE TABLE projects (
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    project_code VARCHAR(30) NOT NULL,
    PRIMARY KEY (tenant_id, project_id),
    UNIQUE (tenant_id, project_code)
);

CREATE TABLE tasks (
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    task_number INTEGER NOT NULL,
    title VARCHAR(200) NOT NULL,
    PRIMARY KEY (tenant_id, project_id, task_number),
    FOREIGN KEY (tenant_id, project_id)
      REFERENCES projects(tenant_id, project_id)
);

Here the tenant is part of every scoped identity. Project code is unique per tenant, task numbers restart per project, and the task relationship cannot cross tenant boundaries.

Why the Worked Model Is Strong

The project primary key gives child tables an enforceable tenant-scoped reference. The alternate project-code rule preserves a user-facing business identifier. The task key states that one number identifies one task only within a project.

If a global generated task ID is later introduced, the existing composite uniqueness should remain unless the business permits duplicate task numbers. Physical convenience should not silently weaken domain integrity.

Querying by a Composite Key

Always bind each component as a typed parameter. For batch lookup, use a supported row-value comparison, join to a staged key table, or use a table-valued parameter where available. Avoid constructing a giant string predicate.

Explain plans should confirm that indexes serve the expected leading columns. Statistics on correlated columns may also matter because independent estimates can misjudge combinations such as tenant and local ID.

Bulk Loading and ETL

Bulk pipelines should deduplicate by the complete business tuple before loading and still rely on database constraints for final protection. Staging tables can preserve rejected rows with reason codes instead of silently discarding them.

When constraints are temporarily disabled for an approved bulk process, validate all data before re-enabling them and prevent normal traffic from observing an invalid state. Leaving constraints untrusted or disabled creates long-term risk.

Replication and Event Consumers

Repeated messages are common in at-least-once delivery. A composite UNIQUE constraint such as (source_system, event_id) can provide an idempotency boundary, while a transaction records both the event and its effects.

Select components from a stable producer contract. Timestamps or mutable payload values are poor idempotency keys because retries may differ in formatting without representing a new event.

Observability for Constraint Failures

Monitor violations by constraint name, operation, service, release, and tenant without placing confidential values in metrics. A sudden rise may indicate a retry defect, changed normalization, stale client behavior, or incorrect migration ordering.

Constraint telemetry is valuable because it records attempts to violate domain boundaries. It should lead to root-cause correction rather than automatic removal of the rule.

Security Implications

Composite tenant relationships provide defense in depth, but they do not replace authorization. A valid tuple proves referential consistency, not that the current user may access it.

Keep tenant filters, row-level policies, ownership checks, and service authorization aligned with the same scope. Test that changing either tenant or local ID cannot disclose or modify another customer's data.

Evolution and Backward Compatibility

When a new key component is introduced, older applications may not supply it. A safe rollout can add the column, backfill a deterministic value, update writers and readers, validate data, and only then enforce NOT NULL and the new composite constraint.

Use an expand-and-contract sequence when zero-downtime compatibility is required. Avoid changing key shape in one deployment while old application versions are still active.

Real-World E-Commerce Pattern

A marketplace product can use a surrogate product ID and UNIQUE (seller_id, sku). Inventory can use UNIQUE (warehouse_id, product_id). Order items may use PRIMARY KEY (order_id, line_number).

Each combination represents a different grain. Reusing one generic key pattern across all tables would miss those domain boundaries.

Real-World Appointment Pattern

UNIQUE (provider_id, appointment_start) prevents one provider from having two appointments beginning at the same instant. It does not prevent overlapping appointments of different lengths.

State exactly what the constraint guarantees and what additional scheduling logic must handle.

Testing Composite Constraints

For composite UNIQUE, repeat each component independently and verify only duplicate complete tuples fail. For foreign keys, test valid pairs, mismatched combinations, missing parents, and partial NULLs. For checks, use a truth table.

Test updates and concurrent inserts, not only creation. Use the exact production DBMS because NULL, collation, and validation behavior differ.

Safe Production Migration

  1. State the row grain and scope in plain language.
  2. Select the minimal stable columns that express it.
  3. Define NULL, collation, type, and ordering semantics.
  4. Profile duplicates, partial keys, and orphan combinations.
  5. Resolve violations according to business meaning.
  6. Review indexes, key width, child propagation, and query paths.
  7. Estimate locking, sorting, logging, and replication impact.
  8. Update applications, ORMs, APIs, ETL, and joins.
  9. Add and validate the constraint using DBMS-safe options.
  10. Monitor violations, performance, and concurrency afterward.

Common Composite Constraint Mistakes

  • Creating two individual UNIQUE constraints instead of one composite rule.
  • Using composite uniqueness when each column must actually be unique.
  • Missing tenant, period, or other required scope.
  • Adding irrelevant columns that weaken uniqueness.
  • Concatenating values instead of preserving typed columns.
  • Ignoring NULL and collation semantics.
  • Joining or referencing only part of the key.
  • Using a mutable, very wide composite primary key everywhere.
  • Adding a surrogate but omitting business uniqueness.
  • Assuming composite UNIQUE prevents temporal overlap.
  • Ignoring soft-delete and partitioning behavior.
  • Adding constraints before cleaning existing data.

Production Design Checklist

  • Define exactly what one row represents.
  • Identify global versus tenant, customer, order, or time scope.
  • Use the minimal complete set of key components.
  • Decide whether every component must be non-null.
  • Verify type, collation, precision, and column order.
  • Choose composite primary key or surrogate plus composite UNIQUE.
  • Map every foreign-key component correctly.
  • Join on the complete relationship.
  • Evaluate width, indexes, updates, and child propagation.
  • Account for soft delete, history, partitioning, and sharding.
  • Profile existing data and test concurrent writes.

Interview Questions and Answers

What is a composite constraint? One constraint defined using two or more columns together.

Does composite UNIQUE make each column unique? No. It protects only the complete combination.

Can a table have a composite primary key? Yes; it remains one primary-key constraint with several components.

What must a composite foreign key reference? A corresponding parent primary or UNIQUE candidate-key combination.

Why does column order matter? It controls mapping and influences index access paths.

Composite key versus surrogate key? Composite keys express natural identity; surrogates simplify propagation. A surrogate should not remove required business uniqueness.

Can composite uniqueness contain NULL? Possibly, but behavior differs by DBMS and should be tested.

Can it prevent date overlap? Ordinary composite UNIQUE prevents duplicate tuples, not general range overlap.

Key Takeaway

Composite constraints enforce one business rule whose meaning depends on several columns together. Their quality depends less on column count than on selecting the exact grain, scope, stability, and NULL semantics of the fact being stored.

RELIABLE COMPOSITE RULE = CORRECT ROW GRAIN
                        + COMPLETE BUSINESS SCOPE
                        + MINIMAL STABLE COMPONENTS
                        + EXPLICIT NULL SEMANTICS
                        + CORRECT TYPE AND ORDER
                        + FULL RELATIONSHIP MAPPING
                        + WORKLOAD-AWARE INDEXING
                        + VALIDATED EXISTING DATA

Use composite primary, unique, foreign-key, and check constraints where the domain is genuinely multi-column. Add surrogates for operational simplicity when needed, but preserve the business combination that makes each fact valid.