cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Durchholz, Joachim" <>
Subject RE: Foreign keys whe migrating
Date Mon, 21 Nov 2011 10:48:59 GMT
>> 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
>> 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:
> 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
Also, it does not allow a COMMIT to go through if any constraints are violated; things will
go into automatic ROLLBACK instead.

View raw message