cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Durchholz, Joachim" <Joachim.Durchh...@hennig-fahrzeugteile.de>
Subject RE: Foreign keys whe migrating
Date Tue, 06 Dec 2011 10:16:24 GMT
> > 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

Mime
View raw message