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.