CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
ericso
ericso

Reputation: 3318

Postgres: how can I create an FK reference on a partial index?

We want to maintain referential integrity between two tables, but we also want to soft delete records. SQL to create the tables:

CREATE TABLE table_a (
    id           SERIAL PRIMARY KEY,
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at   TIMESTAMP WITH TIME ZONE,
    uuid         UUID DEFAULT uuid_generate_v4()
);

CREATE UNIQUE INDEX table_a_uuid ON table_a (uuid) WHERE deleted_at IS NULL;

CREATE TABLE association (
    id               SERIAL PRIMARY KEY,
    created_at       TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at       TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at       TIMESTAMP WITH TIME ZONE,
    table_a_uuid     UUID REFERENCES table_a (uuid) NOT NULL
);

The problem is, Postgres does not allow foreign key references to partial indexes, of which association.table_a_uuid is. Is there any way I can maintain referential integrity between these tables in PSQL, or do I have to do it in the application layer?

Edit: To add some extra context as to why we want to use uuid as the FK ref, soft-deleted records on table_a represent older "versions" of the record, but all "versions" have the same uuid. The referencing association table should "point" to the latest version of the table_a record.

Upvotes: 1

Views: 855

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

There is a rather kludgy way to do what you want. The idea is a two-part index, one with the uuid and the second with the id -- but only for deleted records. For the non-deleted, it has a constant value and -1 is convenient.

They you can construct a foreign key reference to that:

CREATE TABLE table_a (
    id           SERIAL PRIMARY KEY,
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at   TIMESTAMP WITH TIME ZONE,
    id_if_deleted  int generated always as (case when deleted_at is null then -1 else id end) stored,
    uuid         UUID DEFAULT gen_random_uuid()
);

CREATE UNIQUE INDEX table_a_uuid ON table_a (uuid, id_if_deleted) ;

CREATE TABLE association (
    id               SERIAL PRIMARY KEY,
    created_at       TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at       TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at       TIMESTAMP WITH TIME ZONE,
    const_neg_one    int generated always as (-1) stored,
    table_a_uuid     UUID,
    foreign key (table_a_uuid, const_neg_one) REFERENCES table_a (uuid, id_if_deleted )
);

Upvotes: 4

Related Questions