Re: custom integrity check

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
>
>