citusdata / citusdata/citus

Support foreign key ON DELETE SET NULL (col)

Open
#7,132 5 comments 1 reaction 0 assignees View on GitHub
feature
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Citus currently doesn't allow foreign keys on distributed tables to use ON DELETE SET NULL (or SET DEFAULT) when the distribution column is in the referencing columns (as it generally is) because, per @onderkalaci, Citus doesn't support NULL values for the distribution column. However, PostgreSQL 15+ supports the syntax ON DELETE SET NULL (col1, col2, ...) where it will only set the specified columns to NULL (ditto for SET DEFAULT). Citus could support this syntax and allow foreign keys to be created when the distribution column is not in the list of columns to set null.

For example, this currently fails:
```
CREATE TABLE t (
p INT NOT NULL,
i INT NOT NULL,
r INT,
PRIMARY KEY (p, i)
);
SELECT create_distributed_table('t', 'p');
ALTER TABLE t ADD FOREIGN KEY (p, r) REFERENCES t (p, i) ON DELETE SET NULL (r);
```
with this error:
```
ERROR: cannot create foreign key constraint
DETAIL: SET NULL or SET DEFAULT is not supported in ON DELETE operation when distribution key is included
in the foreign key constraint
```

It'd be really nice if it worked. Otherwise, it's hard in some cases to guarantee referential integrity in Citus databases.

Thank you.

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.