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
Date: Tue, 21 May 2002 07:43:59 -0700 (PDT)
Message-ID: <20020521073832.K11485-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <0205211208080R.01320@magnus>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, wagner@imsd.uni-mainz.de
Subject: Re: constraints for ensuring relationships
In-Reply-To: <20020521073832.K11485-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox