Re: foreign keys and inheritance problem

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