Foreign keys in between reference tables are not supported

[handuy]

I have 2 tables
Users:
CREATE TABLE users (id int PRIMARY KEY, name text);
Posts:
CREATE TABLE posts (id int PRIMARY KEY, author_id int REFERENCES users(id));

Let's load some sample data:
INSERT INTO users VALUES (1, 'Duy'), (2, 'Long'), (3, 'Huy');
INSERT INTO posts VALUES (1, 2), (2, 2), (3, 3);

When I create reference tables for the 2 tables:
SELECT create_reference_table('users');
SELECT create_reference_table('posts');

I have an error message:
ERROR: cannot create foreign key constraint because reference tables are not supported as the referencing table of a foreign constraint
DETAIL: Reference tables are only supported as the referenced table of a foreign key when the referencing table is a hash distributed table

Does that mean citus do not support foreign key constraints between reference tables ?

[furkansahin]

Hey @handuy ,
Yes, currently Citus supports foreign keys in two cases;

1. From hash distributed tables to hash distributed tables when the foreign key includes the distribution column,
2. From hash distributed tables to reference tables on any column.

[gfontenay]

We have a couple of dozen tables that would qualify as reference tables and the lack of FKs between them is a showstopper in considering migration to Citus Cloud. This involves genomic data and there are a lot of critical relationships for which we want and need PG enforcement.

[prevostc]

+1, this is quite a big limitation that is not even cited in the documentation. As a general conclusion, it is not possible to use citus in an existing codebase without a significant schema rewrite (at the time of writing)

[marcocitus]

Fixed by a5b087c will be part of the next release.