citusdata / citusdata/citus

Citus should allow creating foreign keys without providing the referenced primary key

Open
#1,224 0 comments 0 reactions 0 assignees View on GitHub
warm-up
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

As we've realized in #1213, currently Citus do not support creating foreign keys without providing the primary key on the `REFERENCES` part of the statement. For instance, currently the following statement errors out on Citus:
```SQL
ALTER TABLE referencing_table ADD CONSTRAINT test_constraint FOREIGN KEY(ref_id) REFERENCES referenced_table ON DELETE CASCADE;
ERROR: cannot create foreign key constraint because referenced column list is empty
+HINT: Add column names to "REFERENCES" part of the statement.
```

On regular PostgreSQL tables, the above statement uses the `PRIMARY KEY` key of `referenced_table` and successfully creates the foreign key.

We should consider adding this feature Citus. Note that, the owner of this issue should consider cases with multiple columns in the statement such as:

```SQL
ALTER TABLE referencing_table ADD CONSTRAINT test_constraint FOREIGN KEY(ref_id, other_id) REFERENCES referenced_table ON DELETE CASCADE;

Or even:

-- other_key is not primary key
ALTER TABLE referencing_table ADD CONSTRAINT test_constraint FOREIGN KEY( other_id) REFERENCES referenced_table (other_id) ON DELETE CASCADE;
```

And some other potential combinations.

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.