Unique constraints and surrogate keys

[pgb]

Hello,
I'm using JBoss 3.2.3 with Oracle 8.
Our DB team usually designs the table with a surrogate key (sequence based) and then adds a UNIQUE constraint to the "natural" key of the table.
For instance, this table:

COUNTRY
 COUNTRY_ID
 CODE
 NAME

will have a sequence for COUNTRY_ID, and a UNIQUE constraint for CODE (as that's the natural key).
How can I map this with JBoss? Currently I mapped COUNTRY_ID as the pk-field, but whenever JBoss tries to insert a new record with a duplicated CODE value, I'll get a EJBCreateException (and not a duplicate key error, as I'll need to show the users).
Is there any way to map this DB design into JBoss?

What other suggestions do you have?

Thanks in advance.

[dhartford]

In the past, I write into the code these kind of checks before insertion. i.e. send a ValueObject to a Session bean to handle all create/updates of values for your Entities, and have the Session bean return descriptive errors for constraints you know will exist. You could check through lookup if the UNIQUE constraints are met from Java opposed to relying on the CMP engine to catch all errors, as well as add any other Validations that may presently be coded into the database (regex formating and other things DBA's like to do using proprietary Database tools).

-D

[pgb]

I wanted to use the database for the unique constraints in order to "save" the DB access of a finder method that checks the existance of a similar record.

Do you know of any other alternative that does not require another hit to the DB?

Thanks for you quick answer.

[aloubyansky]

What is the SQL state reported by SQLException.getSQLState()?

I think you can try using

 <entity-command name="no-select-before-insert"/>

In case INSERT failed the state is analysed and if it is 23000 we guess it's a duplicate key, though, it's not always the case.

[pgb]

Thanks Alexey, that works great!
Howerver, I'm not able to add the entity command on the defaults section.
Also, is there any side-effect for turning this option on?
Where can I read more about it?
This is my jbosscmp-jdbc-defaults.xml file (XDoclet is correctly merging it):

<defaults>
 <datasource>java:/OracleDS</datasource>
 <datasource-mapping>Oracle8</datasource-mapping>
 <create-table>false</create-table>
 <remove-table>false</remove-table>
 <pk-constraint>true</pk-constraint>
 <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
 <entity-command name="no-select-before-insert" class="org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand">
 <attribute name="SQLExceptionProcessor">jboss.jdbc:service=SQLExceptionProcessor</attribute>
 </entity-command>
</defaults>

It works fine if I put the entity-command on each bean, though.

[pgb]

I tested it a little bit more, and got a new question:
It now fails when inserting a new record. Is there any way I can get a DuplicateKeyException on an update?
The reason we use surrogate keys is partially because we can allow users to later modify the code, that would otherwise have been the primary key on the table.

Any chance of having this behaviour?

Thank you

[aloubyansky]

What is the problem using entity-command in defaults? Have a look at standardjbosscmp-jdbc.xml. It follows the same dtd.

A side effect is that you will get DuplicateKeyException for any SQL exception with the 23000 code. Refer to Oracle's docs on what this code is used for (generally it is integrity constraint violation).

There is no way to get DuplicateKeyException on update as it's nonsense from the CMP point of view. PK is not allowed to be modified.

[pgb]

"loubyansky" wrote:
What is the problem using entity-command in defaults? Have a look at standardjbosscmp-jdbc.xml. It follows the same dtd.

I really don't know what the problem is.
I tried:

<defaults>
 <datasource>java:/OracleDS</datasource>
 <datasource-mapping>Oracle8</datasource-mapping>
 <create-table>false</create-table>
 <remove-table>false</remove-table>
 <pk-constraint>true</pk-constraint>
 <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
 <entity-command name="no-select-before-insert" />
</defaults>

besides and it doesn't work.

[aloubyansky]

Any exception at deployment/runtime? Is it a snippet from standardjbosscmp-jdbc.xml? if so, is it overriden in jbosscmp-jdbc.xml?

[pgb]

No, not a single exception at deployment nor runtime.
It is a snippet from my jbosscmp-jdbc.xml
(it's working properly, as the specified datasource is correctly used (and it's not in standardjbosscmp-jdbc.xml).

Thanks again