OpenJPA
  1. OpenJPA
  2. OPENJPA-1387

Unique colums automatically defined as non-nullable

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.3, 2.0.0-M3
    • Fix Version/s: 1.2.3, 2.0.0-beta
    • Component/s: sql
    • Labels:
      None
    • Environment:
      Error happens in every environment.
    • Patch Info:
      Patch Available

      Description

      Any unique constraint declared with OpenJPA implicitly sets the respective columns to be non-null. This is a problem if OpenJPA's mapping tool is used to create the database schema. Even if something like @Column(nullable=true) is explicitly stated, the columns will always be created by OpenJPA as not null.

      Modifying the database schema manually to make the columns nullable is a possible workaround. It does not seem to cause any problems with either OpenJPA or the databases.

      I would suggest to drop the marking of unique columns as not nullable. This is done by removing the corresponding code lines in org.apache.openjpa.jdbc.schema.Unique (trivial patch appended to this issue). If someone wants a unique column to be not nullable, this can be specified explicitly with @Column(nullable=false) as usual.

      I can only speculate about the reason this strange coupling of unique and nullable had been introduced into OpenJPA. To my knowledge, it is perfectly legal use to have unique contraints on nullable columns. In effect this means that there may be multiple rows with null values, whilst all rows with non-null values have to be unique. ANSI SQL-92 also explicitly mentions nullable unique columns and states that this is a crucial difference between PRIMARY KEY columns and UNIQUE columns: the former are always not nullable, the latter may be nullable.

      This issue also pops up again and again in the user discussions, without (to my knowledge) a single authorative answer to why this behaviour is as it is in OpenJPA. Two examples:
      My question, which remained unanswered:
      http://n2.nabble.com/Unique-colums-automatically-made-NOT-NULL-td2827809.html
      Another users question, about a month later:
      http://n2.nabble.com/Nullable-unique-constraints-td3161182.html

      1. OpenJPA-Trunk_OJ1387.patch
        0.7 kB
        Martin Dirichs
      2. openjpa-test.zip
        8 kB
        Mark Struberg

        Issue Links

          Activity

          Hide
          Michael Dick added a comment -

          Once the issue has been marked as fixed in the release notes it's confusing to have it show up again. Rather than using the same issue I've created a new JIRA (OPENJPA-1651) to discuss the new variation..

          I'm re-closing this one. Sorry for the noise.

          Show
          Michael Dick added a comment - Once the issue has been marked as fixed in the release notes it's confusing to have it show up again. Rather than using the same issue I've created a new JIRA ( OPENJPA-1651 ) to discuss the new variation.. I'm re-closing this one. Sorry for the noise.
          Hide
          Mark Struberg added a comment -

          A few tips on my sample project:
          The NOT NULL can be checked by simply executing
          $> mvn openjpa:sql
          which generates the database creation SQL script in
          ./target/database.sql

          to execute the project you need to create the database upfront in MySQL with
          create database TestDb;
          user 'root' with no password is assumed (persistence.xml)

          After
          $> mvn clean install
          you will see the myString column as empty string instead of NULL when doing a
          mysql> select * from NullFieldTable;
          ------------+

          PK myString

          ------------+

          1  

          ------------+
          1 row in set (0.00 sec)

          Show
          Mark Struberg added a comment - A few tips on my sample project: The NOT NULL can be checked by simply executing $> mvn openjpa:sql which generates the database creation SQL script in ./target/database.sql to execute the project you need to create the database upfront in MySQL with create database TestDb; user 'root' with no password is assumed (persistence.xml) After $> mvn clean install you will see the myString column as empty string instead of NULL when doing a mysql> select * from NullFieldTable; --- ---------+ PK myString --- ---------+ 1   --- ---------+ 1 row in set (0.00 sec)
          Hide
          Pinaki Poddar added a comment -

          Reopening as per further discussion.

          Show
          Pinaki Poddar added a comment - Reopening as per further discussion.
          Hide
          Mark Struberg added a comment -

          it seems that this bug is still not fixed (tested with latest from SVN).

          This test project will show the problems.

          There are 2 issues which are not yet resolved

          • Unique columns doesn't need to be mandatory (currently they are always generated as NOT NULL)
          • NOT NULL columns of type String which are not filled will get stored as empty string (instead of throwing an Exception). See OPENJPA-525
          Show
          Mark Struberg added a comment - it seems that this bug is still not fixed (tested with latest from SVN). This test project will show the problems. There are 2 issues which are not yet resolved Unique columns doesn't need to be mandatory (currently they are always generated as NOT NULL) NOT NULL columns of type String which are not filled will get stored as empty string (instead of throwing an Exception). See OPENJPA-525
          Hide
          Martin Dirichs added a comment -

          Just noticed that there exists an exceptional database not capable of unique contraints on nullable fields: Apache Derby, versions up to 10.4.1.3. In 10.4.1.3, this shortcoming was fixed, see here:
          http://issues.apache.org/jira/browse/DERBY-3330
          OpenJPA currently uses Derby 10.2.2.0 for its test cases. Thus, there are build failures after applying this patch. There are three possibilities of how to deal with this:
          1. Eliminate the problem by updating to a more recent Derby version.
          2. Modify the test cases so that @Column(nullable=false) is explicitly stated where necessary.
          3. Continue to label columns implicitly as non-nullable as database-specific logic. Do it for Derby < 10.4.1.3 and maybe some other database out there I am not aware of, leave it for all others (default).

          Show
          Martin Dirichs added a comment - Just noticed that there exists an exceptional database not capable of unique contraints on nullable fields: Apache Derby, versions up to 10.4.1.3. In 10.4.1.3, this shortcoming was fixed, see here: http://issues.apache.org/jira/browse/DERBY-3330 OpenJPA currently uses Derby 10.2.2.0 for its test cases. Thus, there are build failures after applying this patch. There are three possibilities of how to deal with this: 1. Eliminate the problem by updating to a more recent Derby version. 2. Modify the test cases so that @Column(nullable=false) is explicitly stated where necessary. 3. Continue to label columns implicitly as non-nullable as database-specific logic. Do it for Derby < 10.4.1.3 and maybe some other database out there I am not aware of, leave it for all others (default).

            People

            • Assignee:
              Heath Thomann
              Reporter:
              Martin Dirichs
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development