From: Casey T. Deccio <ctdecci@sandia.gov>
To: pgsql-sql@postgresql.org
Subject: Re: table constraints
Date: Mon, 28 Feb 2005 12:23:35 -0800
Message-ID: <1109622215.32226.11.camel@boomerang.ran.sandia.gov> (raw)
In-Reply-To: <20050228202016.GA32034@wolff.to>
References: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
<20050228202016.GA32034@wolff.to>
On Mon, 2005-02-28 at 13:20 -0700, Bruno Wolff III wrote:
> 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).
>
Thanks for the input. This may work in the example I've given.
However, the example I provided was contrived and was used merely to
show the discrepancy that I'm finding with using the function as a
constraint. In the larger example, things are a bit more complex, and
I've found using such a constraint a better fit for now for the problem
I'm working with.
That said, I'd like to know why the constraint I provided isn't working
with the corresponding example.
> 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.
Thanks for the tip. Good point. I've never really known when to use
text over varchar, so though I began using varchar for potential later
porting to other rdbms, and since then it's just been tradition...:)
Casey
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: ctdecci@sandia.gov
Subject: Re: table constraints
In-Reply-To: <1109622215.32226.11.camel@boomerang.ran.sandia.gov>
* 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