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
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
.
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
.