Re: CREATE TABLE with REFERENCE

On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:

> On Tuesday 29 Jul 2003 2:30 am, Stephan Szabo wrote:
> > On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:
> > > Stephan Szabo wrote:
> > > >On Mon, 28 Jul 2003, kay-uwe.genz wrote:
> > > >>Second question. Is there a method of INSERT INTO both tables VALUES
> > > >>without group them in the same Transaction?
> > > >
> > > >You mean insert a row in each table that acts as the pk row for the
> > > > other?
> > > >
> > > >You could fake it by inserting one in with a NULL for the fk column
> > > >(unless they're both NOT NULL), inserting the other and then updating
> > > > the first.  Otherwise I think you need to be running in a single
> > > > transaction (although they could be grouped inside a function or as a
> > > > trigger for example).
> > >
> > > But isnt' foreign key constraints deferrable inside transactions?
> > > i vaugely remember doing it  . istn' it the best way of doing it?
> > > [ if at all it works :-) ]
> >
> > Yes, that'd be the best way (the constraint must be deferrable in that
> > case and you often want it to be initially deferred for cyclic
> > constraints).  The question seemed to specifically ask if there was a way
> > without grouping them into a transaction.
>
> But if the columns are marked NOT NULL it wont' be possible to
> insert at the first place itself? i think in such cases one of the
> columns must allow NULL , as someone has already pointed out.

For doing it in multiple transactions using NULL yes (I mention that
above).  You could fake past that by using a non-NULL dummy value that has
a pk row that doesn't have real meaning except for this purpose, but at
that point you probably might as well not bother with the constraint.