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);
Presumably, you intend:
Notes:
user_id
is unique, so there is no need for a composite primary key.id
to include the table name. That way most foreign key references will have the same name as the primary key.serial
togenerated always as identity
. This is now recommended for Postgres.