How can I add a foreign key constrain on a inherited table in postgresql?

Question

I am using inherited tables in a project using posgresql.

Something like that :

create table root
(
    id bigserial,
    some_data text,
    ...
);

create table leaf_a
(
    data2 text
) inherits(root);

create table leaf_b
(
    maybe_other_data text
) inherits(root);

Everything works great so far.

But I recently added a table that is a one-to-one relation that if used on leaf_a and leaf_b, so I created like that :

create table conf
(
    id bigserial,
    root_id bigint,
    more_data text
);

So far so good, but now I want to create a constraint :

alter table conf
    add constraint plop foreign key (root_id) references root (id);

Postgres lets me create the constraint.

So I add some data :

insert into leaf_a (some_data, data2) values ('...', '...');

Lets say that the id generated (the id from the root table) is 42, I want now to add data to the table conf :

insert into conf (root_id, more_data) values (42, '...');

And here is the problem, postgres tells me that there is no data with id = 42 in table root.

Ok, so how can I work around this problem ?

Thanks in advance.

Answer 1

Redesigning database schema (root have a link the conf table, so do leaf_a and leaf_b) and adding 3 constraints (on root, leaf_a and leaf_b) works for me.