PostgreSQL UNIQUE for JOINed table

Question

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

Answer 1

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.

Answer 2

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.