Composite FK referencing 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

Per the relational model, attributes from a parent table are only present in a child table if that attribute forms part of the primary key of the parent table.

If the primary key of User is user_id, but account_id must be migrated to a child entity to maintain referential integrity, then you must define the primary key to be (user_id, account_id) and demote the existing primary key (user_id) to an alternate key (unique constraint).

In practical terms, most RDBMS will allow you to create a foreign key constraint to any candidate key (unique constraint) so the selection of which key is "primary" isn't as strict.

In your case, don't migrate account_id unless referential integrity from a child object of Account is required. If you need the account_id for a User you can easily get it via a join.

Answer 2

This unique constraint seems unnecessary to me as user_id is already unique.

While on the face if it what you are looking for is fine, it breaches the concept of primary / unique / foreign keys by not being minimal. As the FK doesn't need the second property as the first is unique, it could cause misunderstanding by including it. There are also implementation specific issues: while the engine could store “actually, use this key instead it is unique enough” or derive that upon use, it would be implementing a bunch of checks to enable an edge case that is uncommon (unheard of, if enforcing minimal keys).

So while DB engines could implement what you are wanting here, I doubt any do.

---

That you are trying to enforce the link between user_id and account_id in the referencing table suggests that account_id does not belong in that table. Instead bring it in via a join or view when you need both when referencing via calendars. If you want to have account_id in that table directly as you think that will be more efficient, or because your app's data layer doesn't support joins/views in a way that makes those options possible, perhaps you could create the column as a persisted computed column either using built-in support if postgres has it or via triggers. Otherwise creating the extra unique index is your only option.