Re: constraints for ensuring relationships

On Tue, 21 May 2002, Markus Wagner wrote:

> I wonder if I can use constraints to ensure some special integrity.
>
> Suppose there is a table "Type" of item types:
>
> CREATE TABLE "Type"
> (
>  "index"    SERIAL PRIMARY KEY,
>  "name"     TEXT,
>  "multiple" BOOL
> );
>
> Suppose there is a table "Item" like this:
>
> CREATE TABLE "Item"
> (
>  "index"    SERIAL PRIMARY KEY,
>  "type"     INT REFERENCES "Type",
>  "data"     TEXT
> );
>
> Supose there is a table "SubItem" that contains special details about our
> items:
>
> CREATE TABLE "SubItem"
> (
>  "index" SERIAL PRIMARY KEY,
>  "Item"  INT REFERENCES "Item",
>  "Data"  TEXT
> );
>
> And now the constraint:
>
> Each Item should be allowed to have more than one subitem, if and only if its
> type allows for multiple instances, i. e. the type the item is linked to must
> have 't' in its field 'multiple'.
>
> Can I attach constraints to some of the tables (e. g. "SubItem") that ensure
> this?

Given your schema, you might be able to get away with triggers that do the
check or a bunch of check constraints that call a function that does the
check.  You need to worry about updates on Type and Item and inserts and
updates on SubItem I think.