citusdata / citusdata/citus

Foreign keys between distributed tables on non-distribution columns are not supported

Open
#4,159 1 comment 0 reactions 0 assignees View on GitHub
application-development
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

This issue is kind of obvious, but sill wanted to open an issue to track. I don't think it is easily solvable, plus per query overhead of supporting this feature could be very high.

The details of the supported foreign keys are nicely documented [here](http://docs.citusdata.com/en/latest/develop/reference_ddl.html#adding-removing-constraints) so no need to re-document here.

Citus currently allows foreign keys as long as the `referencing` and `referenced` data reside on the same physical nodes and relies on Postgres to enforce the constraints between shards. For example, it is the reason that any foreign key to reference tables are supported as reference tables are replicated to every node.

When the relevant data is on another node, like this issue describes the foreign keys are on non-distribution keys, Citus cannot easily enforce and error out as follows:

```SQL
CREATE TABLE users (id text PRIMARY KEY,
name text,
created_at timestamptz DEFAULT now(),
last_see timestamptz);

CREATE TABLE todos (id bigserial PRIMARY KEY,
title text,
is_completed boolean,
is_public boolean,
created_at timestamp DEFAULT now(),
user_id text);

SELECT create_distributed_table('users', 'id');
SELECT create_distributed_table('todos', 'id');

alter table "public"."todos"
add constraint "todos_user_id_fkey"
foreign key ("user_id")
references "public"."users"
("id") on update restrict on delete restrict;
ERROR: referenced table "users" must be a distributed table or a reference table
DETAIL: To enforce foreign keys, the referencing and referenced rows need to be stored on the same node.
HINT: You could use SELECT create_reference_table('users') to replicate the referenced table to all nodes
```

These types of constraints can be auto-generated by frameworks.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.