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

        Thomas Dudziak made changes -
        Fix Version/s 1.0 RC1 [ 12312482 ]
        Fix Version/s 1.0 [ 12310891 ]
        Thomas Dudziak made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Hide
        Thomas Dudziak added a comment -

        As tested via TestConstraints#testPrimaryKeyAutoIncrementColumn()

        Show
        Thomas Dudziak added a comment - As tested via TestConstraints#testPrimaryKeyAutoIncrementColumn()
        Thomas Dudziak made changes -
        Component/s Core (No specific database) [ 12310804 ]
        Thomas Dudziak made changes -
        Fix Version/s 1.0 [ 12310891 ]
        Thomas Dudziak made changes -
        Description An identity column should be a primary key too! The method shouldGeneratePrimaryKeys on SqlBuilder don't allow this. I took the liberty to change the description a bit to make the bug clearer
        Summary An identity column should be an primary key The PK constraint should be generated even if a column is an identity column (except for Hsqldb)
        Thomas Dudziak made changes -
        Status Resolved [ 5 ] Reopened [ 4 ]
        Resolution Won't Fix [ 2 ]
        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
        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.
        Thomas Dudziak made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Won't Fix [ 2 ]
        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 -

        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...
        Juliano Julio created issue -

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development