Re: custom integrity check

Hi Michael,

Thanks for that, it looks like it should work fine. I will give it ago :)

Thanks,

Abdul-Wahid


On Fri, 21 Jan 2005 14:59:25 +0100, Michael Kleiser <mkl@webde-ag.de> wrote:
>  CREATE TABLE cats_items (
>  cat_id int4 NOT NULL, item_id int4 NOT NULL, FOREIGN KEY (cat_id)
> REFERENCES cats (cat_id), FOREIGN KEY (item_id) REFERENCES items (item_id),
> PRIMARY KEY (cat_id, item_id) );
>  CREATE TABLE items_master_cats ( cat_id int4 PRIMARY KEY item_id int4 NOT
> NULL, UNIQUE KEY(cat_id) FOREIGN KEY (cat_id) REFERENCES cats_items(cta_id)
> FOREIGN KEY (item_id) REFERENCES cats_items(item_id) ); ALTER TABLE
> cats_items ADD constraint fk_imc_ci FOREIGN KEY ( cat_id ) REFERENCES
> cat_items;
>
>  cat_items still contains all relationsships including the masters.
> items_master_cats only the masters.
>
>  Because of the constraint 'fk_imc_ci'  it should not be possible to have an
> entrie in
>  cat_items if the cat_id of the entrie is not in masters.
>  ( To solve hen-and-egg-Problem this contraint is defered, so you can have
> this situation, but
>  you can't commit it. )
>
>  You can  add  plpg-procedures , rules, trigger and/or views to
>  hide this complicated data-model from the users.
>
>
>
>
>
>  Abdul-Wahid Paterson wrote:
>  Hi, I have the following table as a link table between my 'cats' table and
> my 'items' table. Every item must have at least one cat and exactly one
> 'master' cat. How can I create an integrity check to make sure that each
> item has exactly one 'master' cat. CREATE TABLE cats_items ( cat_id int4 NOT
> NULL, item_id int4 NOT NULL, master boolean DEFAULT 'f', FOREIGN KEY
> (cat_id) REFERENCES cats (cat_id), FOREIGN KEY (item_id) REFERENCES items
> (item_id), PRIMARY KEY (cat_id, item_id) ); Thanks, Abdul-Wahid
> ---------------------------(end of broadcast)--------------------------- TIP
> 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>