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?