Can i refer FOREIGN KEY to partitioned table in Postgresql v13?

Question

Im trying to add foreign key between 2 partitioned table and it gives me error (there is no unique constraint matching given keys for referenced table "user") im using postgresql v13, pgAdmin 4.26. partitioned table to normal table foreign key works, normal table to partitioned table same error as above. When i read the v13 doc there is no limitation about it. Can anyone help me ?

CREATE TABLE user (
     id serial,
     value varchar,
     PRIMARY KEY (id, value),
     UNIQUE (id)
) PARTITION by LIST(value);

CREATE TABLE test (
     id serial,
     user_id integer,
     PRIMARY KEY (id, int),
     UNIQUE (int, id),
     FOREIGN KEY (user_id) REFERENCES user(id)
) PARTITION by LIST(user_id);

Answer 1

Presumably, you intend:

CREATE TABLE users (
     user_id int generated always as identity primary key,
     value varchar,
) ;

CREATE TABLE tests (
     test_id int generated always as identity,
     user_id integer,
     PRIMARY KEY (test_id, user_id)
     FOREIGN KEY (user_id) REFERENCES user(user_id)
) ;

Notes:

• I removed the partitioning logic. It does not seem particularly useful and it interferes with your table definitions. The partitioning key needs to be part of the primary key.
• I changed the primary key to have only one column in the first table. The user_id is unique, so there is no need for a composite primary key.
• I changed the names of the primary keys from id to include the table name. That way most foreign key references will have the same name as the primary key.
• I pluralized the names of the tables. This helps avoid conflicts with SQL key words and reserved words. Plus, it makes sense because tables contain multiple entities.
• I changed serial to generated always as identity. This is now recommended for Postgres.

Answer 2

You cannot have a unique constraint on id (every primary key or unique constraint has to contain value), so you also cannot reference that column in a foreign key.

The only solution to have a foreign key constraint is to add value to test and include it in the foreign key definition.

There is no workaround for that.

By the way, you should never have a table named user as that is a reserved SQL key word.