db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Foreign key constraints
Date Tue, 09 Oct 2007 13:20:39 GMT
Williamson, Nick wrote:
> Hi all,
> 
> I see that in Derby, foreign key constraints don't support the deferred
> / deferrable syntax that I'm used to using in Oracle, and - creating
> data in IJ by running a script - validation seems to take place straight
> away by default (in other words, Derby doesn't wait until the COMMIT).
> Now what if I have a transaction inserting into several tables and one
> of the tables that I'm inserting into has an FK constraint pointing at
> one of the others? It would seem to be the case that unless I issue all
> the INSERTs in the "right" order, they will fail. Similarly, if I have a
> circular dependency where two tables each have an FK constraint pointing
> at the other one, I would imagine that I will never be able to insert
> data into those tables. Is there a way of changing Derby's behaviour
> with regard to the time when FK constraints are enforced?

Hello Nick,

I think you are out of luck on this one...
There is a Jira for it, but it has not seen any activity: 
https://issues.apache.org/jira/browse/DERBY-532

If you only need the deferred constraint checking in an initial 
population phase, you can of course add the constraint after the data is 
inserted. You should make sure the data is consistent then, I'm not sure 
Derby tells you which row(s) violate(s) the constraint when it fails the 
constraint addition.
Repeatedly dropping and recreating the indexes on a database seems 
unfeasible to me, but for some situations it might be acceptable (?).


I don't know of any plans for implementing this functionality, but you 
are free to comment or vote on the issue, or do something else to help 
the cause :)


regards,
-- 
Kristian

NB! There used to be some restrictions on ALTER TABLE operations. I hope 
there are none left that makes the proposed approach unusable.

> 
> TIA
> Nick


Mime
View raw message