> > 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). > > I guess doing this inside Cayenne OracleAdapter on commit might be helpful > for the Oracle users. And yeah, also wish other DB's would support deferred > constraint checking.
Oh, the Oracle users can specify deferred checking on the constraints, so if they want deferred checking, they don't need support from Cayenne for that. It's probably best to leave these settings alone. (I was a bit imprecise above, the "database default" is: revert to whatever was specified on each constraint. Which usually means CONSTRAINTS ALL IMMEDIATE because few application programmers are aware of deferred checking.) > BTW the original issue that Christian described was happening during DDL > operations (vs DML we are discussing here). DDL could be an entirely > different beast. Ah, sorry, I missed that. And yes, that's a different beast; on most databases, DDL is non-transactional. (The only exception I'm aware of is Postgresql.) For Oracle, as for most other databases, the solution for DDL would be this: 1) Remove all constraints that will become invalid, via ALTER TABLE 2) DROP/CREATE/ALTER TABLE to change the table structure 3) Create new constraints via ALTER TABLE I just found a good comprehensive explanation, including Oracle syntax examples, on http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html . Regards, Jo
