Unique constraint that includes serial primary key in postgresql

Question

I have a postgresql tables with the following layout:

create table bar(
    bar_id serial primary key,
    ... other columns
)
create table foo(
    foo_id serial primary key,
    bar_id bigint not null,
    ... other columns
)
create table baz(
    baz_id serial primary key,
    foo_id bigint not null references foo(foo_id),
    bar_id bigint not null references bar(bar_id),
    constraint fk_fb foreign key (foo_id, bar_id) references foo(foo_id, bar_id)
)

I want to refer to both foo_id and bar_id in another table (baz) and have a foreign key constraint, so I need to add a unique constraint to (foo_id, bar_id). The fact that foo_id is a primary key guarantees that the combination of foo_id and bar_id is unique, even if every single value of bar_id is the same. My question is if there is a performance hit to adding the unique constraint on (foo_id, bar_id), or if postgresql is smart enough to know that the fact that foo_id is unique across the table by virtue of being the primary key means that there is no need to do anything with bar_id.

The table foo contains rows that are not present in baz, so dropping the bar_id from the foo table won't work.

I want to refer to both foo_id and bar_id in another table: why? The serial column is enough to uniquely identify a row in the foo table. — GMB
@GMB Maybe so that an ON UPDATE CASCADE will keep foo and baz in sync? (Not saying that it's a good idea) — Bergi
"Dropping the bar_id from the foo table won't work." - but how about dropping the bar_id column from the baz table? And making foo.bar_id reference bar? — Bergi

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-03-03T16:56:25

There is a performance penalty for adding another UNIQUE constraint, because such a constraint is implemented with an index that needs to be updated for every data modification on the table.

One thing that you could consider if DML performance on this table is at a premium is defining the primary key over both columns. Then you would lose the uniqueness guarantee for foo_id, but you don't have to pay the price for an extra index.

Perhaps you can also come up with an alternative data model that does not require you to reference both columns with a foreign key, like GMB suggested in his answer.

Unique constraint that includes serial primary key in postgresql

1 Answers