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);

A foreign key must match **all** columns of the referenced primary key. (also `user` is a reserved keyword, you need to quote it, in order to be able to use it as a table name `"user"` not `user`) — , Oct 19 '20 at 07:58
https://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref — , Oct 19 '20 at 08:01
I'd rather ask why you have value as a part of the pk in the user table. (Since id is unique, value adds nothing.) — jarlh, Oct 19 '20 at 08:01
@jarlh: that's an annoying restriction in Postgres: the partitioning key must be part of the primary key (but I wonder why a table named `user` needs partitioning in the first case) — , Oct 19 '20 at 08:02
thats an exaple i need multiple partition tables and refer them :D any solution on this case ? — Orgil, Oct 19 '20 at 08:04
The solution is in the link I have given: https://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref I am inclined to close this as a duplicate, as the underlying problem isn't partitioning but how to specify a foreign key constraint — , Oct 19 '20 at 09:12
FIgured out value doesnt have to be unique i just have to add 2 foreign keys (user_id and user_value) in test becoase value is in my primery key thank you alot — Orgil, Oct 19 '20 at 10:13
You actually don't have to quote reserved names if you access them using schema.table format for example create table public.user... — Joe Love, Dec 03 '21 at 18:02

score 2 · Answer 1 · answered Oct 19 '20 at 10:56

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.

Gordon Linoff · Answer 2 · 2020-10-19T11:10:58.233

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.

That's what Orgil _intended_, but that's not possible: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c26a2b58f6c295871a347c7e18ca4c1a — , Oct 19 '20 at 11:09

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

2 Answers2