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)

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