Date: Tue, 21 May 2002 07:43:59 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Markus Wagner <wagner@imsd.uni-mainz.de>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: constraints for ensuring relationships
In-Reply-To: <0205211208080R.01320@magnus>
Message-ID: <20020521073832.K11485-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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.