Without "deferred constraints" there is not much other way around it
(AFAIK)...

I came in a bit late on the tread... Unless you are talking about large data
dumps rather than day to day live operation, then removing and re-adding FK
constraints would seem a huge performance hit... Not to mention the "object
in use" hassles...

The Data Definition Guide talks about working with circular referential
integrity (page 103 in my version) and states the workaround as suggested
with the Nulls... Though it goes on about "Use ALTER TABLE to modify the
EMPLOYEE table. Change the DEPT_NO column from NULL to '2'" it its example,
which would tend to confuse!

Regards
Colin



-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@...]
Sent: Tuesday, 16 March 2004 11:54 a.m.
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Insert without referential constraints

Guys guys...

> One way to get around it, if you are not already doing it, is to allow

NULLs

> in one of the foreign keys, make your inserts with a Null, keeping
> track

of

> the ids, then do an update on the fields that you inserted NULLs into,
> all within a transaction so it would all get undone if anything failed.

Are we trying to figure out ways to get inconsistent data into the database?
Then why not drop the FKs to start with?

--
Martijn

> Regards
> Colin
>
> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@...]
> Sent: Monday, 15 March 2004 9:49 p.m.
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Insert without referential constraints
>
> Hi,
>
> > Is it possible to insert a bunch of records related to each other
> > via foreign keys without worrying about the order of inserts?
> >
> > I've heard that with some databases it's possible to set up a
> > transaction so that referential constraints are checked only at the
> > end of the transaction. Is something like this possible with
> > Firebird also?
>
> These constraints are called "deferred constraints", something which
> Firebird doesn't (yet) support.
>
> It also doesn't support turning constraints OFF.
>
> > Or would the only option be dropping the constraints before inserts
> > and creating them again after?
>
> That's the only way.
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL &
> MS

SQL

> Server.
> Upscene Productions
> http://www.upscene.com

Yahoo! Groups Links






######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################