Re: custom integrity check

[Michael Kleiser <mkl@xxxxxxxxxxx>]

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