0

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.

iXô
  • 1,133
  • 1
  • 16
  • 39
  • 2
    Have you already read documentation chapter Data Definition/Inheritance/Caveats ? It mentions limitations in this scenario. – Tomasz Myrta Oct 03 '13 at 21:41
  • Ok, I have switch the way the link between conf, root, and leaves is made. Now I a constraints on each leaf table and root. – iXô Oct 04 '13 at 07:58
  • 1
    @iXô Does it work for you? If yes, could you add this as an answer and accept it? – Evgeniy Chekan Oct 04 '13 at 11:27

1 Answers1

0

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.

iXô
  • 1,133
  • 1
  • 16
  • 39