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(at)webde-ag(dot)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
>