Composite FK referencing atomic PK + non unique attribute

Question

I am trying to create the following tables in Postgres 13.3:

CREATE TABLE IF NOT EXISTS accounts (
    account_id Integer PRIMARY KEY NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    user_id Integer PRIMARY KEY NOT NULL,
    account_id Integer NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS calendars (
    calendar_id Integer PRIMARY KEY NOT NULL,
    user_id Integer NOT NULL,
    account_id Integer NOT NULL,
    FOREIGN KEY (user_id, account_id) REFERENCES users(user_id, account_id) ON DELETE CASCADE
);

But I get the following error when creating the calendars table:

ERROR:  there is no unique constraint matching given keys for referenced table "users"

Which does not make much sense to me since the foreign key contains the user_id which is the PK of the users table and therefore also has a uniqueness constraint. If I add an explicit uniqueness constraint on the combined user_id and account_id like so:

ALTER TABLE users ADD UNIQUE (user_id, account_id);

Then I am able to create the calendars table. This unique constraint seems unnecessary to me as user_id is already unique. Can someone please explain to me what I am missing here?

Answer 1

Postgres is so smart/dumb that it doesn't assume the designer to do stupid things.

The Postgres designers could have taken different strategies:

• Detect the transitivity, and make the FK not only depend on users.id, but also on users.account_id -> accounts.id. This is doable but costly. It also involves adding multiple dependency-records in the catalogs for a single FK-constraint. When imposing the constraint(UPDATE or DELETE in any of the two referred tables), it could get very complex.
• Detect the transitivity, and silently ignore the redundant column reference. This implies: lying to the programmer. It would also need to be represented in the catalogs.
• cascading DDL operations would get more complex, too. (remember: DDL is already very hard w.r.t. concurrency/versioning)

From the execution/performance point of view: imposing the constraints currently involves "pseudo triggers" on the referred table's indexes. (except DEFERRED, which has to be handled specially)

So, IMHO the Postgres developers made the sane choice of refusing to do stupid complex things.