example.sql

CREATE TABLE a (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE aa (
    a UUID NOT NULL,
    at TIMESTAMPTZ NOT NULL DEFAULT statement_timestamp(),
    PRIMARY KEY (a, at),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id)
) INTERLEAVE IN PARENT a (a);

CREATE TABLE ab (
    a UUID NOT NULL,
    at TIMESTAMPTZ NOT NULL DEFAULT statement_timestamp(),
    PRIMARY KEY (a, at),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id)
) INTERLEAVE IN PARENT a (a);

CREATE TABLE ac (
    a UUID NOT NULL,
    aa TIMESTAMPTZ,
    ab TIMESTAMPTZ,
    PRIMARY KEY (a),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id),
    CONSTRAINT fk_aa FOREIGN KEY (a, aa) REFERENCES aa (a, at),
    CONSTRAINT fk_ab FOREIGN KEY (a, ab) REFERENCES ab (a, at)
) INTERLEAVE IN PARENT a (a);

result.log

CREATE TABLE
CREATE TABLE
CREATE TABLE
pq: column "a" cannot be used by multiple foreign key constraints
Error: pq: column "a" cannot be used by multiple foreign key constraints
Failed running "sql"

workaround.sql

CREATE TABLE a (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE aa (
    a UUID NOT NULL,
    at TIMESTAMPTZ NOT NULL DEFAULT statement_timestamp(),
    PRIMARY KEY (a, at),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id)
) INTERLEAVE IN PARENT a (a);

CREATE TABLE ab (
    a UUID NOT NULL,
    at TIMESTAMPTZ NOT NULL DEFAULT statement_timestamp(),
    PRIMARY KEY (a, at),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id)
) INTERLEAVE IN PARENT a (a);

CREATE TABLE ac (
    a UUID NOT NULL,
    a1 UUID NOT NULL CHECK (a = a1),                            -- <====== duplicate 'a' here with a check() statement
    aa TIMESTAMPTZ,
    ab TIMESTAMPTZ,
    PRIMARY KEY (a),
    CONSTRAINT fk_a FOREIGN KEY (a) REFERENCES a (id),
    CONSTRAINT fk_aa FOREIGN KEY (a, aa) REFERENCES aa (a, at),
    CONSTRAINT fk_ab FOREIGN KEY (a1, ab) REFERENCES ab (a, at) -- <====== use the duplicate 'a1' instead in the FK constraint
) INTERLEAVE IN PARENT a (a);

workaround_result.log

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

The expected result is that all rows in ac match:

• one and only one row in a
• zero or one row in aa
• zero or one row in ab

You can also delete any row in ac without deleting a matching row in a, aa, or ab, but you cannot delete a row from a, aa, or ab if there is a row in ac that matches (without updating or deleting the row in ac in the same transaction).