CODEWITHSUNDEEP
postgresqlinheritancepostgresql-9.3
Nikša Baldun
Nikša Baldun

Reputation: 1894

"Abstract" table in PostgreSQL (inheritance)

There are a lot of PostgreSQL inheritance questions on SO, but I wanted to be clear about my particular case. Suppose I have the following tables:

CREATE TABLE abstract_person (
    id bigint PRIMARY KEY,
    ver int NOT NULL,
    code varchar NOT NULL,
    id_country bigint NOT NULL,
    ...
);

CREATE TABLE abstract_person_phone (
    id bigint PRIMARY KEY,
    ver int NOT NULL,
    phone varchar NOT NULL,
    id_abstract_person bigint NOT NULL
);

CREATE TABLE individual (
    first_name varchar NOT NULL,
    last_name varchar NOT NULL,
    ...
    CONSTRAINT individual_pkey PRIMARY KEY (id),
    CONSTRAINT individual_id_country_fkey FOREIGN KEY (id_country) REFERENCES country (id),
    CONSTRAINT individual_unique UNIQUE (code) DEFERRABLE INITIALLY DEFERRED
) INHERITS (abstract_person);

There will be more inheriting tables, individual is just one example. The table abstract_person won't ever be written to directly. Am I correct in the following assumptions:

  1. Child tables do not inherit unique, primary key, and foreign key constraints, but I have resolved that by declaring those constraints on child table directly.

  2. About enforcing referential integrity between abstract_person and abstract_person_phone tables, I can do that with triggers. I can't use a trigger on parent table, I'd have to attach it to every child table, but I can use a single trigger function, like so:

    CREATE OR REPLACE FUNCTION person_deleted() RETURNS trigger AS $BODY$
BEGIN
    DELETE FROM abstract_person_phone WHERE id_abstract_person = OLD.id;
    RETURN OLD;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER individual_deleted_trigger AFTER DELETE ON individual
    FOR EACH ROW EXECUTE PROCEDURE person_deleted();

Upvotes: 2

Views: 3268

Answers (1)

onerror
onerror

Reputation: 616

Yes, you're completely right. All these constraints are not propagated to child tables. As for triggers, you can use them on child tables as you wrote. And you could use a trigger on the parent table that would decide in which of the child tables it should put data on an insert query (or retrieve on select) based on some conditions

Upvotes: 1

Related Questions