Foreign Key referencing inherited table

Question

I have the following tables:

CREATE TABLE mail (
    id serial,
    parent_mail_id integer,
    ...

    PRIMARY KEY (id),
    FOREIGN KEY (parent_mail_id) REFERENCES mail(id),
    ...
);

CREATE TABLE incoming (
    from_contact_id integer NOT NULL REFERENCES contact(id),
    ...
    PRIMARY KEY (id),
    ---> FOREIGN KEY (parent_mail_id) REFERENCES mail(id), <---
    ...
) INHERITS(mail);

CREATE TABLE outgoing (
    from_user_id integer NOT NULL REFERENCES "user"(id),
    ...  
    PRIMARY KEY (id),
    --> FOREIGN KEY (parent_mail_id) REFERENCES mail(id), <--
    ...
) INHERITS(mail);

incoming and outgoing inherit from mail and define their foreign keys (and primary keys) again, as they are not automatically inherited.

The problem is:

If I'd insert an incoming mail, it is not possible to reference it from the outgoing table as the foreign key only works with the super table (mails).

Is there a workaround for that?

Answer 1

PostgreSQL 9.3 docs:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:

If we declared cities.name to be UNIQUE or a PRIMARY KEY, this would not stop the capitals table from having rows with names duplicating rows in cities. And those duplicate rows would by default show up in queries from cities. In fact, by default capitals would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to capitals, but this would not prevent duplication compared to cities.

Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to capitals. In this case you could work around it by manually adding the same REFERENCES constraint to capitals.

Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

And not really a workaround, so maybe make mails a non-inherited table, and then separate incoming_columns and outgoing_columns for their respective extra columns, with the mail id as both their primary and foreign key. You can then create a view outgoing as mail INNER JOIN outgoing_columns, for example.

Answer 2

You may use a constraint trigger

CREATE OR REPLACE FUNCTION mail_ref_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mail WHERE id = NEW.parent_mail_id
        ) THEN
        RAISE foreign_key_violation USING MESSAGE = FORMAT('Referenced mail id not found, mail_id:%s', NEW.parent_mail_id);
    END IF;
    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE CONSTRAINT TRIGGER mail_fkey_trigger
    AFTER UPDATE OR INSERT ON incoming
    DEFERRABLE
    FOR EACH ROW EXECUTE PROCEDURE mail_ref_trigger();