9 Replies Latest reply on Jun 11, 2004 9:33 AM by Pablo Bendersky

    Unique constraints and surrogate keys

    Pablo Bendersky Newbie

      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.

        • 1. Re: Unique constraints and surrogate keys
          darren hartford Expert

          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

          • 2. Re: Unique constraints and surrogate keys
            Pablo Bendersky Newbie

            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.

            • 3. Re: Unique constraints and surrogate keys
              Alexey Loubyansky Master

              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.

              • 4. Re: Unique constraints and surrogate keys
                Pablo Bendersky Newbie

                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.

                • 5. Re: Unique constraints and surrogate keys
                  Pablo Bendersky Newbie

                  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

                  • 6. Re: Unique constraints and surrogate keys
                    Alexey Loubyansky Master

                    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.

                    • 7. Re: Unique constraints and surrogate keys
                      Pablo Bendersky Newbie

                       

                      "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.

                      • 8. Re: Unique constraints and surrogate keys
                        Alexey Loubyansky Master

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

                        • 9. Re: Unique constraints and surrogate keys
                          Pablo Bendersky Newbie

                          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