Postgres: How to do Composite keys?

sql

data-integrity

constraints

composite-keys

byAnton Shumikhin·Oct 16, 2024

To create a composite key in Postgres, designate multiple columns within a PRIMARY KEY constraint:

CREATE TABLE example_table (
    column_a data_type,
    column_b data_type,
    PRIMARY KEY (column_a, column_b)
);

This enforces a unique combination of column_a and column_b values for each row.

Creating the composite key

Crafting a composite key necessitates deciding on suitable columns, data types and constraints. Consider a 'users_roles' table where integer identifiers represent user_id and role_id:

CREATE TABLE users_roles (
    user_id INTEGER, -- aka Mighty User
    role_id INTEGER, -- aka Role of Destiny
    PRIMARY KEY (user_id, role_id)
);

Each id likely references a separate users and roles table as a foreign key. Postgres automatically generates an implicit index to fast track queries involving the composite key.

Naming constraints & readability

You can name your constraints to enhance code quality and maintainability. For instance:

CREATE TABLE example_table (
    column_a INTEGER, -- our First warrior ;)
    column_b VARCHAR(255), -- the Second one joins the battle :D
    CONSTRAINT pk_example PRIMARY KEY (column_a, column_b)
);

Remarking CONSTRAINT pk_example is optional. However, it's handy when you need to refer to the constraint in future, such as when you want to modify or discard it.

Data integrity with composite keys

Composite keys secure data integrity by enforcing that each row's values combination is unique. For a users_roles table, the same user_id can't be attributed the same role_id repeatedly. This secures referential integrity in a many-to-many relationship scenario.

Here are tips for integration of composite keys optimally:

Data Types: Ensure suitable data type for each column within the composite key: INTEGER for IDs, VARCHAR for textual identifiers.
Auto Sequences: Use the SERIAL keyword for columns requiring automatic incrementation, preventing manual sequence errors.
Uniqueness: Despite being part of a composite key, each column should still function independently preserving atomicity.

Power of constraints in action

By using unique and primary key constraints, composite keys guard against repeated combinations of data. This is key for structure like junction tables in a many-to-many scenario.

Here's how you can supercharge your schema design:

PRIMARY KEY vs UNIQUE: Use PRIMARY KEY for uniqueness and not-null enforcement. Opt for UNIQUE to allow nulls but maintain uniqueness.
Relational Integrity: Leverage composite keys to encapsulate the essence of entity relationships in your model, building a resilient and logical schema.