>> For Oracle, the constraint needs to be configured for "deferrable" checking >> (available since 8i, don't know how well it works today). That postpones >> constraint checks to commit time, and I think it is not the default. >> In other words, in a legacy schema, Oracle's constraint checking will be on >> a per-DML-statement basis. >> SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint >> checking to the next commit (the Oracle docs claim this is ANSI SQL92 syntax >> and semantics). >> Oracle in general is bad at reporting what data exactly caused some >> constraint violation; deferring checks to commit time is going to make that >> worse if at all possible. >> >> Just enumerating the issues. >> I sure would like to see deferred checking in Cayenne. I have absolutely no >> idea whether it's worth the challenges though.
> How is this a Cayenne issue? Isn't that just a property of the database as a > whole which you set up at config time? 1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just the currently running session, until the next COMMIT (at which point SET CONSTRAINTs is reset to whatever the database default is, usuall SET CONSTRAINTS ALL IMMEDIATE). 2) It is a Cayenne issue in that Cayenne issues COMMIT commands. Also, in that Cayenne's operation is affected by the setting - if constraint checking is deferred, Cayenne could do INSERTs/UPDATEs/DELETEs in any order. > After having spent a long time struggling with this in mysql when upgrading > schemas, I have to say it would be a very nice feature to have in my db of > choice. Oh, I forgot to mention the mysql workaround: > > 1. Set the db constraints to OFF > 2. Do your schema stuff > 3. Set the db constraints back to ON > 4. Check that you didn't break anything: > > http://forge.mysql.com/tools/tool.php?id=11 > > That procedure works really well. foreign_key_checks is a system variable. In other words: you switch it, you affect all sessions, not just your currently running transaction. The given script checks whether the data is inconsistent now, but it must check ALL data in the database. That's too much to run after each commit. The Oracle setting just checks those rows that were actually modified. That should be MUCH faster. Also, it does not allow a COMMIT to go through if any constraints are violated; things will go into automatic ROLLBACK instead. Regards, Jo
