Re: constraints for ensuring relationships - Mailing list pgsql-sql

From Stephan Szabo
Subject Re: constraints for ensuring relationships
Date
Msg-id 20020521073832.K11485-100000@megazone23.bigpanda.com
Whole thread Raw
In response to constraints for ensuring relationships  (Markus Wagner <wagner@imsd.uni-mainz.de>)
List pgsql-sql
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.




pgsql-sql by date:

Previous
From: "Joel Burton"
Date:
Subject: Re: drop constraint problem
Next
From: "Josh Berkus"
Date:
Subject: Re: Bug with Daylight Savings Time & Interval