Re: CREATE TABLE with REFERENCE

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.

regds
mallah.