Disabling foreign key constraints

[Robert Enyedi <ro...@intland.com>]

Is there a way in Derby to disable foreign key constraints?

This should be something similar to the SET FOREIGN_KEY_CHECKS = 0 
statement in MySQL which, when set, makes the engine to ignore any 
foreign key constraints.

I know that having the database constraints is the way to go, but for 
some scenarios (e.g. unit tests) having partially valid data is just fine.

Regards,
Robert

RE: Disabling foreign key constraints

[de...@segel.com]

> -----Original Message-----
> From: Daniel Morton [mailto:djmorton42@yahoo.com]
> Sent: Wednesday, August 16, 2006 8:24 AM
> To: Derby Discussion; msegel@segel.com
> Subject: RE: Disabling foreign key constraints
> 
> IMO, this feature would be quite beneficial, as it is
> very useful for bulk loading data that you know to
> already conform to your constraints.  That way, you
> can load the data into the various tables in whichever
> way is most convenient, rather than worrying about
> ensuring that all the child records exist before
> parent records are loaded, which can get very
> complicated, particularly when you are dealing with a
> large number of tables and foreign-key relationships.
> 
> Dan Morton
> 
[SNIP]
[mjs] 
Ah, but there are other ways of performing a fast load that doesn't require
adding the feature of disabling of foreign key constraints.

If you were going to look at adding a HPL (High Performance Loader) then
you're probably going to want to look at revamping how Derby stores data on
disk. 

What I am suggesting is that when you look at adding a new "feature" that
you don't become myopic, focusing only on that feature and then cobbling it
in to the database code stream. 

RE: Disabling foreign key constraints

[Daniel Morton <dj...@yahoo.com>]

IMO, this feature would be quite beneficial, as it is
very useful for bulk loading data that you know to
already conform to your constraints.  That way, you
can load the data into the various tables in whichever
way is most convenient, rather than worrying about
ensuring that all the child records exist before
parent records are loaded, which can get very
complicated, particularly when you are dealing with a
large number of tables and foreign-key relationships.

Dan Morton

--- derby@segel.com wrote:

> So let me get this straight....
> 
> You want a feature request to allow the
> user/developer to turn off
> constraint checking on demand? 
> 
> The example you give is for unit testing....
> 
> This really isn't a good feature, or one that should
> ever be used.
> 
> Using your example, you want to turn off the
> constraints that will exist in
> production for a "unit test". This leads to the
> scenario that you could
> perform unit tests without checking/testing with the
> constraints in place.
> You really should perform unit tests with the
> constraints in place.
> 
> The other issue is that the database is more than
> the sum of its parts. You
> have to think of the database as more than just
> object persistence, and
> while I'm not suggesting this is the case for
> Robert, I do believe that even
> on this list which is a subsection of Derby
> users/developers, that the
> "persistence vision" exists.
> 
> The other issue, when looking at a feature, you
> still have to consider the
> TCO of the feature. Not just in maintenance, but
> also in the increase in
> footprint size and weigh it against the value that
> it brings. You're adding
> excess baggage.
> 
> I wonder what ever happened to the idea of making
> Derby more modular where
> you can set/unset features that you don't want in
> your production copy.
> This level of functionality doesn't come cheap, but
> it will definitely make
> things "interesting".
> 
> But hey! What do I know? I'm using POJO for a BPR
> POC project. Or maybe I'm
> just old fashioned when it comes to design ... ;-)
> (BPR = Business Process Re-engineering, POC = Proof
> of Concept)
> 
> -G
> 
> P.S. MySQL is a db that "we" should not emulate when
> looking at features.
> (Yes I *am* an Informix bigot, but I also tolerate
> some others. ;-)
> 
> > -----Original Message-----
> > From: Robert Enyedi
> [mailto:robert.enyedi@intland.com]
> > Sent: Wednesday, August 16, 2006 7:14 AM
> > To: Derby Discussion
> > Subject: Disabling foreign key constraints
> > 
> > Is there a way in Derby to disable foreign key
> constraints?
> > 
> > This should be something similar to the SET
> FOREIGN_KEY_CHECKS = 0
> > statement in MySQL which, when set, makes the
> engine to ignore any
> > foreign key constraints.
> > 
> > I know that having the database constraints is the
> way to go, but for
> > some scenarios (e.g. unit tests) having partially
> valid data is just fine.
> > 
> > Regards,
> > Robert
> 
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

RE: Disabling foreign key constraints

[de...@segel.com]

So let me get this straight....

You want a feature request to allow the user/developer to turn off
constraint checking on demand? 

The example you give is for unit testing....

This really isn't a good feature, or one that should ever be used.

Using your example, you want to turn off the constraints that will exist in
production for a "unit test". This leads to the scenario that you could
perform unit tests without checking/testing with the constraints in place.
You really should perform unit tests with the constraints in place.

The other issue is that the database is more than the sum of its parts. You
have to think of the database as more than just object persistence, and
while I'm not suggesting this is the case for Robert, I do believe that even
on this list which is a subsection of Derby users/developers, that the
"persistence vision" exists.

The other issue, when looking at a feature, you still have to consider the
TCO of the feature. Not just in maintenance, but also in the increase in
footprint size and weigh it against the value that it brings. You're adding
excess baggage.

I wonder what ever happened to the idea of making Derby more modular where
you can set/unset features that you don't want in your production copy.
This level of functionality doesn't come cheap, but it will definitely make
things "interesting".

But hey! What do I know? I'm using POJO for a BPR POC project. Or maybe I'm
just old fashioned when it comes to design ... ;-)
(BPR = Business Process Re-engineering, POC = Proof of Concept)

-G

P.S. MySQL is a db that "we" should not emulate when looking at features.
(Yes I *am* an Informix bigot, but I also tolerate some others. ;-)

> -----Original Message-----
> From: Robert Enyedi [mailto:robert.enyedi@intland.com]
> Sent: Wednesday, August 16, 2006 7:14 AM
> To: Derby Discussion
> Subject: Disabling foreign key constraints
> 
> Is there a way in Derby to disable foreign key constraints?
> 
> This should be something similar to the SET FOREIGN_KEY_CHECKS = 0
> statement in MySQL which, when set, makes the engine to ignore any
> foreign key constraints.
> 
> I know that having the database constraints is the way to go, but for
> some scenarios (e.g. unit tests) having partially valid data is just fine.
> 
> Regards,
> Robert