Citus should allow creating foreign keys without providing the referenced primary key
- 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
Assessment
This issue has not been assessed yet.