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

Question

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.

Gordon Linoff · Accepted Answer · 2021-06-17 19:46:28Z

4

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 )
);

answered Jun 17, 2021 at 19:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ericso Over a year ago

Ah, that's an interesting idea. It could even been simplified to a is_deleted boolean field.

Gordon Linoff Over a year ago

@ericso . . . That would not work because it would allow. only one deleted record.

Jasen Over a year ago

it would work if you set it to null on deleted records instead of to true.

Jasen Over a year ago

is live might be a better name than is_deleted however. it also saves on storage as null columns use none.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related