Friday, October 25, 2002

I love learning something new. Today I learned that as of Oracle 8 you can defer constraints within a transaction until commit is called. That means that within a transaction you can violate the restraints as long as everything is squared away before the changes are committed. The key is defining the restraints with the DEFERRABLE and INITIAL DEFERRED key words. See the ORACLE8 FOR DEVELOPERS PDF file. This is nice compromise between DBA's that never want the rules broken and Weblogic that expects things to happen it's way. Being stuck between a DBA and your appserver is like well this.

Using Container Managed Persistence (CMP) and Container Managed Relationships (CMR) I found that Weblogic 6.1 sp 3 violates the rule that foreign keys (FK) can't be null. If you let Weblogic auto deploy your tables you will notice it will not create the FK NOT NULL constraint. However if you have a good DBA, and I'm lucky to work with two of them, then you'll be forced to use that constraint to prevent orphaned records. This where deferring the constraint until commit saves your bacon.

Weblogic wants to create the child record and then update it's FK with the parent id. So it does an insert and then an update followed by a commit. With out a deferred constrain the insert fails. With a deferred constraint everything should be tickety-boo (That's Albertan for just fine). Oh one last detail, DataSources in Weblogic have AutoCommit on by default, so make sure to us a TxDataSource as they have AutoCommit set to false.


No comments: