Postgres: 부분 색인에 FK 참조를 만드는 방법은?

질문:

우리는 두 표 사이의 인용의 완전성을 유지하고 싶지만 기록을 소프트하게 삭제하기를 원한다.테이블의 SQL 문을 만들려면 다음과 같이 하십시오.
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
);
문제는 Postgres에서 외부 키 인용 부분 인덱스를 허용하지 않는다는 것입니다. 그 중에서 association.table_a_uuid 는 그렇습니다.PSQL에서 이 표들 사이의 인용 완전성을 유지할 방법이 있습니까? 아니면 응용 층에서 이렇게 해야 합니까?
편집: 추가 상하문을 추가하여 우리가 uuid을 FK 참고로 사용하는 이유를 설명하기 위해 table_a의 소프트 삭제 기록은 기록된 비교적 오래된'버전'을 표시하지만 모든'버전'은 똑같다uuid.참조association표는 최신 버전의 table_a 기록을 가리켜야 한다.

답안

네가 하고 싶은 일을 하는 데는 상당히 서투른 방법이 있다.이 아이디어는 두 부분으로 구성된 색인으로 일부는 uuid, 두 번째 부분은 id이지만 삭제된 기록만 대상으로 한다.삭제되지 않은 것은 상수 값이 있고 -1 매우 편리합니다.
외부 키 참조를 구성할 수 있습니다.
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 )
);