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.