Foreign key constraint on two columns to restrict ALTER and INSERT to certain values

In postgres I want to restrict the possible insertion and altering of a value in a certain column to a given combination in another table.

I have table A. user_id is unique, project_id is not unique.

-- table A
default_project_id    user_id    place    start         ...
1                     1          Berlin   01.01.2023    ...
1                     2          Rome     01.01.2023    ...
2                     3          Rio      ...           ...
3                     4          ...      ...           ...
3                     5          ...      ...           ...

Then there is table B. user_id nor project_id are not unique.

-- table B
project_id     user_id
1              1
2              1
3              1
2              2
5              3
4              4
9              4
1              ...

Now, I want to add a CONSTRAINT to table A that does the following: Only allow default_project_id for a user_id, that has a matching project_id and user_id combination in table B. I thought a foreign key constraint on the two columns default_project_id and user_id on table A referencing project_id and user_id on table B would do it, but I get a

There is no unique constraint matching given keys for references table table B

How would I accomplish this?

Solution

According to DDL-CONSTRAINTS-FK:

A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. (...)

So the reason you get the error There is no unique constraint matching given keys for references table table B is because you don't have unique constraint on the field pair project_id, user_id. While none of them is unique in your database, I daubt there is a need to have the same project assigned to the same user twice, so the pair (project_id, user_id) should be made unique. This may be done by adding a UNIQUE CONSTRAINT.

ALTER TABLE B
  ADD CONSTRAINT user_in_project_unique UNIQUE (user_id, project_id);

After that you can create a foreign key.

ALTER TABLE A 
  ADD CONSTRAINT fk_users_project 
    FOREIGN KEY (user_id, default_project_id) REFERENCES B(user_id, project_id);