Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2013
    Posts
    10

    Primary Key from "generated by default" to "generated always"

    Hello,
    I am trying to move a database from MySQL to DB2 and I am confrunting with the following problem (I will describe it as simple as possible):
    I created a table TEST with the columns (ID, SUBJECT_ID)
    In order to keep all the IDs from the old (mysql) table, I created the DB2 table like this:
    CREATE TABLE TEST (
    ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,
    SUBJECT_ID INTEGER NOT NULL
    CONSTRAINT PK_TEST PRIMARY KEY ( ID) ,
    CONSTRAINT FK_TEST_SUBJECT FOREIGN KEY (SUBJECT_ID) REFERENCES SUBJECT (ID) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION )

    Then I inserted all data from the old table:
    INSERT INTO TEST (ID, SUBJECT_ID) VALUES
    (1, 2), (2, 8), ...... (18133, 4259)

    Now I need the ID to be generated always when inserting new values for SUBJECT_ID.
    So I altered the ID column:
    ALTER TABLE TEST ALTER COLUMN ID SET GENERATED ALWAYS
    but when I am trying to insert a new row I get this error:

    SQL0803N One or more values in the INSERT statement, UPDATE statement, or
    foreign key update caused by a DELETE statement are not valid because the
    primary key, unique constraint or unique index identified by "1" constrains
    table "TEST" from having duplicate values for the index key.
    SQLSTATE=23505

    I must mention that the old ID column does not contain all the values from 1 to 18133. There are some big gaps in this interval.
    And another important thing: if I insert just few lines (up to 9 or 10 I think) from the old table, then, on a new insert (INSERT INTO TEST (subject_id) VALUES (9040)), the new ID is automatically generated.

    My DB2 version is v9.7.0.0, on LinuxAMD6497

    Thank you!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    You need to set the identity column to the correct value, which is now 1 since you created the table with "START WITH 1".

    alter TEST alter ID restart with xxx
    (where xxx is the highest value already used plus 1)

    You should upgrade to fixpack 8, since you appear to using fixpack 0. There are about 400 bugs fixed in every fixpack, so in your case that is about 3200 bugs in your version of DB2 that have been fixed in FP 8. Fixpacks are cumulative, so you only need to upgrade to the latest.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2004
    Posts
    47
    Try the following:

    ALTER TABLE YOUR_TABLE_NAME DROP PRIMARY KEY!

    ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)!

    ALTER TABLE YOUR_TABLE_NAME ADD CONSTRAINT PK PRIMARY KEY (ID)!

    SELECT MAX(ID)+1 FROM YOUR_TABLE_NAME!

    ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID RESTART WITH "MAX(ID)+1" (replace with value from last sentence"!


    HTH

  4. #4
    Join Date
    Jan 2013
    Posts
    10
    Thank you both very much!
    It works without dropping and creating the primary key.
    You saved me a lot of time.
    Have a nice rest of day!

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    Quote Originally Posted by CCMF View Post
    Try the following:

    ALTER TABLE YOUR_TABLE_NAME DROP PRIMARY KEY!

    ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)!

    ALTER TABLE YOUR_TABLE_NAME ADD CONSTRAINT PK PRIMARY KEY (ID)!

    SELECT MAX(ID)+1 FROM YOUR_TABLE_NAME!

    ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID RESTART WITH "MAX(ID)+1" (replace with value from last sentence"!


    HTH
    If one had to drop the PK and recreate it just to reset the identity column START number, identity columns would be useless.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •