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
