Re: almost there on a design

On Mon, 3 Mar 2003, Dennis Gearon wrote:

> so make a column in EACH table,
> that references the primary key of the other table,
> then set one of the constraints deferrable,
> write the table with that one first - inside of a transaction
>
> Is that it?

Well, in your case (IIRC) you can't do a straight reference
in both directions because only one of the constraints is actually
a foreign key unless you can guarantee that there will always be a row
in usremailaddrs where the primary flag is true for a given user if there
are any rows for a given user in usremailaddrs, but in general yes, make
the usremailaddrs foreign key initially deferred, do the other check in
whatever fashion and insert the usremailaddrs rows first inside a
transaction.

>
>
> 3/3/2003 4:18:43 PM, Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
>
> >
> >On Mon, 3 Mar 2003, Dennis Gearon wrote:
> >
> >> Is it possible to do 'cross constraints' between two tables using a transaction and:
> >>
> >> SETCONSTRAINTSALLDEFERRED;
> >>
> >> Or making both tables INITIALLY DEFERRED?
> >
> >You make constraints INITIALLY DEFERRED, but yes in general, although
> >technically you only need to make one of the constraints deferred unless
> >you don't want to constraint the order that the rows are made.
> >
> >PostgreSQL doesn't support deferred check constraints, and in general the
> >deferred trigger stuff isn't really meant for end-user use (it's really
> >there for supporting foreign keys), so you'll probably want to make the
> >foreign key from usremailaddrs->usrs be initially deferred and insert the
> >usremailaddrs row first.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
>
>