Re: Best practices?

On Wed, Oct 01, 2003 at 10:57:48AM -0700, David Fetter wrote:
> On Wed, Oct 01, 2003 at 10:28:35AM -0700, elein wrote:
> > A BEFORE trigger is really the way to go here. The trigger function
> > would check that the proper number of bars exists or raise an error.
> >
> > I believe this will work fine both with autocommit and block
> > transactions.
> >
> > The key here is to be sure you enter bars before foos or you will
> > never get any valid foos.
>
> Here's what it looks like (modulo the proprietary bits).
>
> CREATE TABLE foo (
> foo_id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE bar (
> bar_id SERIAL PRIMARY KEY
> , foo_id INTEGER NOT NULL REFERENCES foo(foo_id)
> -- Must have 1 to 4 bars for each foo
> -- If 0 bars, delete the foo; don't allow more than 4 bars per foo.
> );
>
> Does that explain it a little better?

Let me state the obvious:
1. You need to have a foo to create a bar.
2. When you create the foo there will be 0 bars refrencing it.
3. You enter bars therefor assigning bars (1 to 4) to the foo.

So you can't really enforce it in the db, since the db doesn't really
know *when* it is ok not to have any bars and when it's not. Or am I
missing something?

>
> Cheers,
> D
> --
> David Fetter david(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 cell: +1 415 235 3778

--
Dror Matalon, President
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com