From: Bruno Wolff III <bruno@wolff.to>
To: Casey T. Deccio <ctdecci@sandia.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: table constraints
Date: Mon, 28 Feb 2005 14:20:16 -0600
Message-ID: <20050228202016.GA32034@wolff.to> (raw)
In-Reply-To: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
References: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
On Mon, Feb 28, 2005 at 11:28:30 -0800,
"Casey T. Deccio" <ctdecci@sandia.gov> wrote:
>
> In this case each bldg has an owner associated to it, and each animal
> lives in some bldg. Each owner has exactly one own favorite animal out
> of all the bldgs owned by him. So the constraint added to each zoo row
> is that the boolean field 'favorite' is true for exactly once for each
> group of animals in the zoo that have a common owner.
One way to do this kind of thing is to add a UNIQUE key to the table
that holds the animal - table relation of the animal and table and add
a UNIQUE key for the building table of the building and owner.
Then in the owner table add favorite animal building and favorite
animal columns with the property NOT NULL. Then add two deferable
foreign keys (owner, favorite animal building) to the building table
and (favorite animal building, favorite animal) to the animal - building
table. When modifying data you want to change favorites before changing
the underlying tables (where a building or animal change affects someones
favorites).
P.S. in your example you used varchar(50) for the text strings. Using 'text'
is better unless there is a business rule limiting the name lengths to 50
or you expect to port the application to another rdbms.
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: bruno@wolff.to, ctdecci@sandia.gov
Subject: Re: table constraints
In-Reply-To: <20050228202016.GA32034@wolff.to>
* 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