DdlUtils
  1. DdlUtils
  2. DDLUTILS-47

The PK constraint should be generated even if a column is an identity column (except for Hsqldb)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0 RC1
    • Labels:
      None
    • Environment:
      Tested on Hsql and Derby

      Description

      I took the liberty to change the description a bit to make the bug clearer

        Activity

        Hide
        Juliano Julio added a comment -

        Without primary keys, there is no foreign keys, so the FOREIGN KEY statment always return an error...

        Show
        Juliano Julio added a comment - Without primary keys, there is no foreign keys, so the FOREIGN KEY statment always return an error...
        Hide
        Thomas Dudziak added a comment -

        Derby and othr databases support a special type of column - IDENTITY in Derby, autoincrement in MySQL - where the database automatically determines the value of the column if not explicitly specified. You're right that most of the time this is only applicable to primary key columns. However, this is not always the case. For databases like Oracle, the auto-increment is implemented via a sequence, and there is no requirement that the column need to be a primary key.
        Hence, you need to specify both autoincrement and primarykey for the column for databases that require it.

        Show
        Thomas Dudziak added a comment - Derby and othr databases support a special type of column - IDENTITY in Derby, autoincrement in MySQL - where the database automatically determines the value of the column if not explicitly specified. You're right that most of the time this is only applicable to primary key columns. However, this is not always the case. For databases like Oracle, the auto-increment is implemented via a sequence, and there is no requirement that the column need to be a primary key. Hence, you need to specify both autoincrement and primarykey for the column for databases that require it.
        Hide
        Juliano Julio added a comment -

        Exactly! That is the point! If I setPrimaryKey(true) and setAutoincrement(true) this suppose to generate:

        create table TABLE_NAME
        (PK_NAME int generated always (or default) as identity
        ...,
        PRIMARY KEY (PK_NAME)
        );

        But the ddlutils isn't work like this... this is the reason:

        • Determines whether we should generate a primary key constraint for the given
        • primary key columns. By default if there are no primary keys or the column(s) are
        • all auto increment (identity) columns then there is no need to generate a primary key
        • constraint.
        Show
        Juliano Julio added a comment - Exactly! That is the point! If I setPrimaryKey(true) and setAutoincrement(true) this suppose to generate: create table TABLE_NAME (PK_NAME int generated always (or default) as identity ..., PRIMARY KEY (PK_NAME) ); But the ddlutils isn't work like this... this is the reason: Determines whether we should generate a primary key constraint for the given primary key columns. By default if there are no primary keys or the column(s) are all auto increment (identity) columns then there is no need to generate a primary key constraint.
        Hide
        Thomas Dudziak added a comment -

        Ah, ok, now it is clear what the problem is. For Derby and other dbs, you are right, the PK should be there. For Hsqldb, this leads to problems with older versions, and according to the docs the field is automatically a PK column (http://hsqldb.sourceforge.net/doc/guide/ch09.html#create_table-section).

        Show
        Thomas Dudziak added a comment - Ah, ok, now it is clear what the problem is. For Derby and other dbs, you are right, the PK should be there. For Hsqldb, this leads to problems with older versions, and according to the docs the field is automatically a PK column ( http://hsqldb.sourceforge.net/doc/guide/ch09.html#create_table-section ).
        Hide
        Thomas Dudziak added a comment -

        As tested via TestConstraints#testPrimaryKeyAutoIncrementColumn()

        Show
        Thomas Dudziak added a comment - As tested via TestConstraints#testPrimaryKeyAutoIncrementColumn()

          People

          • Assignee:
            Thomas Dudziak
            Reporter:
            Juliano Julio
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development