Skip site navigation (1) Skip section navigation (2)

Re: Best practices?

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Best practices?
Date: 2003-10-01 19:25:16
Message-ID: 20031001192516.GG7302@fetter.org (view raw or flat)
Thread:
Lists: sfpug
On Wed, Oct 01, 2003 at 11:43:40AM -0700, Sean Chittenden 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?
> 
> Insert your foo's before your bars, use a BEFORE trigger to make sure
> the data is as valid as can be, then setup a deferred or AFTER trigger
> that ensures 1 or more bars.  If less than 1 bar, RAISE EXCEPTION...
> -sc

What I do now (yeah, yeah) is start a transaction, make a foo, add
some bars, and commit if that all worked.  However, there's nothing
to prevent me from making a foo, then just leaving the bars hang.  Are
there any good docs on "INITIALLY DEFERRABLE"?

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

In response to

sfpug by date

Next:From: Josh BerkusDate: 2003-10-01 22:44:25
Subject: OT: Desperately seeking sole proprietors?
Previous:From: Sean ChittendenDate: 2003-10-01 18:43:40
Subject: Re: Best practices?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group