Обсуждение: foreign keys and inheritance problem

hi,
I am in some trouble with my tables defined using inheritance, This is a
semplified test case:

-----------------------
create table sub1( name1 text) inherits(father);
create table sub2( name2 text) inherits(father);
create table other (description text, id integer);

-- I know, the contraints is not checked in sub1 and sub2
ALTER TABLE father ADD UNIQUE(id);
ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id);

insert into sub1 (id,name1) VALUES(1,'row1 in sub1');
insert into sub2 (id,name2) VALUES(2,'row1 in sub2');
select * from father;
  id
----
   1
   2

------------------------

I can't insert data in "other" table:
-----------------------------
test=# insert into other(id,description) VALUES(1,'test');
ERROR:  insert or update on table "other" violates foreign key
constraint "other_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "father".
-----------------------------

Is there a way to do this thing? Or I must remove the foreign key
constraint?


thank you
Edoardo

On 12/08/10 18.59, Edoardo Panfili wrote:
> hi,
> I am in some trouble with my tables defined using inheritance, This is a
> semplified test case:
>
> -----------------------
> create table sub1( name1 text) inherits(father);
> create table sub2( name2 text) inherits(father);
> create table other (description text, id integer);
>
> -- I know, the contraints is not checked in sub1 and sub2
> ALTER TABLE father ADD UNIQUE(id);
> ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id);
>
> insert into sub1 (id,name1) VALUES(1,'row1 in sub1');
> insert into sub2 (id,name2) VALUES(2,'row1 in sub2');
> select * from father;
> id
> ----
> 1
> 2
>
> ------------------------
>
> I can't insert data in "other" table:
> -----------------------------
> test=# insert into other(id,description) VALUES(1,'test');
> ERROR: insert or update on table "other" violates foreign key constraint
> "other_id_fkey"
> DETAIL: Key (id)=(1) is not present in table "father".
> -----------------------------
>
> Is there a way to do this thing? Or I must remove the foreign key
> constraint?
>
trigger solution, it seems ok but I am still searching for a declarative
one.

CREATE OR REPLACE FUNCTION insert_veto() RETURNS trigger AS
$BODY$

DECLARE
   present boolean;
BEGIN
    present := exists (select * from father where id=NEW.id) ;
    IF present THEN
        return NULL;
    ELSE
        RETURN NEW;
    END IF;
END
$BODY$ LANGUAGE 'plpgsql'

CREATE TRIGGER veto BEFORE INSERT OR UPDATE ON other FOR EACH ROW
EXECUTE PROCEDURE insert_veto();

Edoardo

Edoardo Panfili <edoardo@aspix.it> writes:
> On 12/08/10 18.59, Edoardo Panfili wrote:
>> I am in some trouble with my tables defined using inheritance,

No, foreign keys do not play very nicely with inheritance.  There is
some explanation in the manual, in the "Caveats" subsection under
"Inheritance" --- see bottom of this page:
http://www.postgresql.org/docs/8.4/static/ddl-inherit.html

            regards, tom lane

On 12/08/10 20.44, Tom Lane wrote:
> Edoardo Panfili<edoardo@aspix.it>  writes:
>> On 12/08/10 18.59, Edoardo Panfili wrote:
>>> I am in some trouble with my tables defined using inheritance,
>
> No, foreign keys do not play very nicely with inheritance.  There is
> some explanation in the manual, in the "Caveats" subsection under
> "Inheritance" --- see bottom of this page:
> http://www.postgresql.org/docs/8.4/static/ddl-inherit.html
>
thank you, I must read with more attenction the page.

I stop the search for a declarative solution, triggers or no check.

thank you again
Edoardo