Postgresql: partial foreign key?

Question

Is it possible to create a partial foreign key (similar to partial indexes) in general sql / postgresql? I have not found a way to force referential integrity in the following case:

Table A is soft-deletable (it has a column deleted_at, which is set to a value when a row is to be marked as such), while Table B is hard-deletable (meaning we actually delete the rows)

table A:

• id
• deleted_at
• some other stuff
• partial unique index for id where deleted_at is null

table B:

• id
• deleted_at
• some other stuff
• partial unique index for id where deleted_at is null

table C:

• referred type
• referred id
• some other stuff

The rows in tables A,B,C represent objects of a certain class. The relation between them that every C needs to reference either an A or a B, marked by the value of the "type" column, and the "id" will mark which instance of the object is referred to.

What I would like to achieve is to force the integrity, so that for all rows in table C where referred type is 'MyData::A', the referred_id must match a row in table A's partial index (where deleted_at is null), similarly for B

Something like

ADD CONSTRAINT name FOREIGN KEY table_C(referred_id)
    REFERENCES table_A(id where deleted_at is not null)
    where referred_type = 'MyData::A'
ADD CONSTRAINT name FOREIGN KEY table_C(referred_id)
    REFERENCES table_B(id where deleted_at is not null)
    where referred_type = 'MyData::B'

Which obviously is bogus syntax.

If this is not possible, would it be possible to do, without messing around with the possibility to have two types of referred objects in different tables, like so:

    CREATE VIEW A_B_ids AS SELECT id, deleted_at From table_A
UNION SELECT id, deleted_at FROM table_B
ADD CONSTRAINT name FOREIGN KEY table_C(referred_id)
REFERENCES A_B_ids(id where deleted_at is not null)

Helsell

Isnt possible, You have to handle that logic in a TRIGGER – Hap

I guess you mean for a table to be x-deletable that when a row in a table becomes in some sense non-current hard means you delete it & soft means you flag it non-current. But you should explain yourself. Ditto fr "partial unique index" & "referred type". Also the rest of your writing is unclear, please use sentences not fragments. (See minimal reproducible example.) – Encroach

Hmm, I thought the meaning of soft deletable is well-defined within the community. Let me clear up the question. – Peyter

Hi. Your business case involves database/sql subtyping/inheritance/polymorphism--google this faq. Your design involves a common aniti-pattern--google re many/multiple/two FKs to many/multiple/two tables. The the soft-delete aspect is additional. PS 1. You must use @xxx to notify user xxx of your comment when they are not the poster & there is more than one other commenter. I just happened to check back here. PS 2. You use "partial FK" as if it meant something--it doesn't. I see "partial index" seems to be a PostgreSQL manual term. 3. Read edit help re code block format via indenting 4 spaces – Encroach

PS 3. It would help if you said the code is bogus before giving it. But this seems moot as you don't explain the bogus syntax, so we don't know what "something like" means. Just use enough sentences and descriptive phrases using appropriate technical terms to actually say what you mean. – Encroach

Answer 1

PostgreSQL's built-in foreign key constraints cannot directly enforce conditions involving other columns or partial data. Triggers provide the flexibility needed for such conditional integrity checks.

You can create a trigger function to enforce the referential integrity based on the 'referred_type' and 'referred_id' in table_C. This function will manually check if the 'referred_id' matches the appropriate 'table_A' or 'table_B' record, based on the 'referred_type'.

For example, you can create a trigger function like this,

CREATE OR REPLACE FUNCTION check_referential_integrity()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.referred_type = 'MyData::A' THEN
        IF NOT EXISTS (
            SELECT 1 
            FROM table_A 
            WHERE id = NEW.referred_id 
              AND deleted_at IS NULL
        ) THEN
            RAISE EXCEPTION 'Referential integrity violation: id % not found in table_A or is marked as deleted.', NEW.referred_id;
        END IF;
    ELSIF NEW.referred_type = 'MyData::B' THEN
        IF NOT EXISTS (
            SELECT 1 
            FROM table_B 
            WHERE id = NEW.referred_id 
              AND deleted_at IS NULL
        ) THEN
            RAISE EXCEPTION 'Referential integrity violation: id % not found in table_B or is marked as deleted.', NEW.referred_id;
        END IF;
    ELSE
        RAISE EXCEPTION 'Unknown referred_type %.', NEW.referred_type;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then, you can create the trigger,

CREATE TRIGGER enforce_referential_integrity
BEFORE INSERT OR UPDATE ON table_C
FOR EACH ROW
EXECUTE FUNCTION check_referential_integrity();

Triclinium