Why is there an error "There is no unique constraint corresponding to the given keys in the target external table 'apartment'"?

Question

PostgreSQL

there are 3 tables :
CREATE TABLE BUILDING(
housing_name CHARACTER VARYING(20) PRIMARY KEY
);
CREATE TABLE APARTMENT( --NUMBER
number_of_apartment INTEGER ,
housing_name CHARACTER VARYING(30) REFERENCES BUILDING (housing_name) ,
PRIMARY KEY(housing_name, number_of_apartment)
);
CREATE TABLE ACCOMMODATION( --RESIDENCE
id serial primary key,
number_of_apartment INTEGER ,
housing_name CHARACTER VARYING(30) ,
FOREIGN KEY (housing_name, number_of_apartment ) REFERENCES APARTMENT (housing_name,
number_of_apartment)
);
Tables 1 and 2 are created without errors, when creating the 3rd one an error occurs:
"the target foreign table 'apartment' does not have a unique constraint corresponding to the given keys"
Although in 'apartment' housing_name and number_of_apartment are a composite key.
How to solve the problem?

Answer 1

The pair of fields that you are trying to use as a foreign key may not have unique values, as a result it will not be clear which row the foreign key refers to.