CODEWITHSUNDEEP
sqlpostgresqlddlunique-constraint
mxcd
mxcd

Reputation: 2404

PostgreSQL UNIQUE for JOINed table

Given the following example:

CREATE TABLE bom (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(255) NOT NULL
);

CREATE TABLE bom_item (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    bom_id_fk INT REFERENCES bom(id) NOT NULL,
    pcb_identifier varchar(50) NOT NULL
)

Is it possible to place an UNIQUE constraint on bom_item.pcb_identifier for a certain bom_id_fk besides making them both PRIMARY KEY?.
The logic behind that is, that a pcb_identifier may only exist once in a certain BOM

Upvotes: 0

Views: 125

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Yes, you want a unique constraint with two keys:

CREATE TABLE bom_item (
    id SERIAL PRIMARY KEY,
    bom_id_fk INTEGER REFERENCES bom(id) NOT NULL,
    pcb_identifier varchar(50) NOT NULL,
    CONSTRAINT unq_pcb_identifier_bom_id_fk UNIQUE (pcb_identifier, bom_id_fk)
);

This will guarantee that at most one pcb_identifier per bom_id_fk. Note that either key individually can be in the table multiple times. It is the pair that is unique.

By the way, Postgres recommends replacing serial is int generated always as identity.

Upvotes: 1

GMB
GMB

Reputation: 222432

In Postgres, you would typically use a partial unique index for this:

create unique index on bom_item(pcb_identifier) where(bom_id_fk = ?)

The question mark should be replaced with the value of bom_id_fk for which you want to enforce uniqueness of pcb_identifier.

Upvotes: 1

Related Questions