Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Identity column on a table

    Hi,

    can some one tell me how to add an identity column to a table (already containing data). I found that this could not be done in a single step. I found some thing , but those are not step by step.

    alter table test2 add column id integer not null with default 0;
    alter table test2 alter column id set generated always as identity;

    error for second step:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0190N ALTER TABLE "QD.TEST2" specified attributes for column "ID"
    that are not compatible with the existing column. SQLSTATE=42837
    db2 =>
    Please let me know how to do this

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    db2 "? SQL0190N"
    ...
    Explanation:
    ...
    * If SET GENERATED ALWAYS AS IDENTITY or SET GENERATED BY DEFAULT AS
    IDENTITY is specified, the column is already defined with a form of
    generation (default, identity, or expression) and there is no
    corresponding DROP in the same statement.
    ...

    You have to drop the default value of the column first.
    Try this:
    Code:
    alter table test2 alter id drop default;
    alter table test2 alter id set generated always as identity;
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Mark Thanks for the reply

    This is the way which i am trying

    db2 => alter table test2 add column id integer not null with default 0
    DB20000I The SQL command completed successfully.
    db2 =>
    db2 =>
    db2 =>
    db2 => alter table test2 alter id drop default
    DB20000I The SQL command completed successfully.
    db2 =>
    db2 =>
    db2 =>
    db2 => alter table test2 alter id set generated always as identity(start with 1,increment by 1)
    DB20000I The SQL command completed successfully.
    db2 =>
    db2 =>
    db2 =>
    db2 =>
    db2 => select * from test2

    PK COL1 ID
    ----------- ----------- -----------
    1 1 0
    2 2 0
    3 3 0
    4 4 0
    5 5 0
    6 6 0
    7 7 0
    8 8 0
    9 9 0
    10 10 0

    10 record(s) selected.

    db2 => reorg table test2
    DB20000I The REORG command completed successfully.
    db2 => select * from test2

    PK COL1 ID
    ----------- ----------- -----------
    1 1 0
    2 2 0
    3 3 0
    4 4 0
    5 5 0
    6 6 0
    7 7 0
    8 8 0
    9 9 0
    10 10 0
    12 12 1
    12 12 2
    13 13 3
    14 14 4

    14 record(s) selected.

    db2 => update test2 set COL1=10 where PK=1
    DB20000I The SQL command completed successfully.
    db2 =>
    db2 =>
    db2 => select * from test2

    PK COL1 ID
    ----------- ----------- -----------
    1 10 0
    2 2 0
    3 3 0
    4 4 0
    5 5 0
    6 6 0
    7 7 0
    8 8 0
    9 9 0
    10 10 0
    12 12 1
    12 12 2
    13 13 3
    14 14 4

    14 record(s) selected.

    Identity values are starting only for the new insertions.But not for existing or updating records.
    I am asking some basic question , kindly excuse me Can we add identity values to an existing rows ?
    I mean can i generate values starting from 1 (not from 0) in the above table?

    Thanks

  4. #4
    Join Date
    Jul 2014
    Posts
    294
    These steps need to be followed to achieve the above

    alter table test2 add column id integer not null with default 0

    alter table test2 alter id drop default

    alter table test2 alter id set generated always as identity(start with 1,increment by 1)

    REORG TABLE TEST2

    update test2 set ID = default

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you should really stay away from the always key word. Its normally best to use default. problems mostly occur when you have referential integrity between this table and others.
    Dave

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by dav1mo View Post
    you should really stay away from the always key word. Its normally best to use default. problems mostly occur when you have referential integrity between this table and others.
    You will also have problems if you want to insert data with your own values for the identity column,

    Andy

Tags for this Thread

Posting Permissions

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