custom integrity check
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
Am Freitag, 21. Januar 2005 12:58 schrieb Abdul-Wahid Paterson:
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.
It seems to me that you should merge this table into the "cats" table.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
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:
Show quoted text
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
Hi Michael,
Thanks for that, it looks like it should work fine. I will give it ago :)
Thanks,
Abdul-Wahid
Show quoted text
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