Re: table constraints

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(at)sandia(dot)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