From: | Sean Chittenden <sean(at)chittenden(dot)org> |
---|---|
To: | Dror Matalon <dror(at)zapatec(dot)com> |
Cc: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Re: Best practices? |
Date: | 2003-10-01 18:43:40 |
Message-ID: | 20031001184340.GF86551@perrin.nxad.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
> > > 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
--
Sean Chittenden
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2003-10-01 19:25:16 | Re: Best practices? |
Previous Message | Dror Matalon | 2003-10-01 18:36:24 | Re: Best practices? |