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