Date: Mon, 28 Feb 2005 14:20:16 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: "Casey T. Deccio" <ctdecci@sandia.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: table constraints
Message-ID: <20050228202016.GA32034@wolff.to>
Mail-Followup-To: "Casey T. Deccio" <ctdecci@sandia.gov>,
	pgsql-sql@postgresql.org
References: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
User-Agent: Mutt/1.5.6i

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.