Thread: 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/

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;

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


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
>