Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    23

    Unanswered: question about IDENTITY

    Hi, my question is :
    Given the table T1, created by:
    CREATE TABLE t1
    (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    c1 CHAR(3)
    )
    The following SQL statements are issued:
    INSERT INTO t1 VALUES (1, 'ABC')
    INSERT INTO t1 VALUES (5, 'DEF')
    Which of the following values are inserted into the ID column by the followin
    g statement?
    INSERT INTO t1(c1) VALUES ('XYZ')
    (Select the correct response)
    A. 0
    B. 1
    C. 2
    D. 5
    E. 6
    I commit the statements in DB 2,the answer is B . But i can not understand GENERATED BY DEFAULT AS IDENTITY .
    Thanks !

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    correct answer is B. 1

    'Generated by default as identity' it means DB2 auto-increment values. So no mater what you insert DB2 will auto-increment by its own auto-increment system. Because first value is inserted "by default" SQL: INSERT INTO t1(c1) VALUES ('XYZ') it gets number 1.

    Don't forget it also exists 'generated always as identity' - this settings doesn't allow user to manually insert values so only DB2 is allowed to auto-increment.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's weird

    what happened to the ABC row? it gets rejected?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    "generated by default" means:

    if user supplies a value, use that value,
    if user supplies no value, generate an value ( starting with 1 and increment by 1 ).

    INSERT INTO t1 VALUES (1, 'ABC')

    is accepted. User supplied a value, row is inserted:

    table contains:
    id , c1
    ------
    1 , 'ABC'

    next insert is:
    INSERT INTO t1 VALUES (5, 'DEF')

    insert is accepted. table now contains:

    id , c1
    ------
    1 , 'ABC'
    5 , 'DEF'

    next insert is:
    INSERT INTO t1(c1) VALUES ('XYZ')

    Db2 acts: "ohh, no value is supplied for column id - I have to generate a value. Let me see, I have to start with value: 1" and inserts a row with id=1

    new table content is:
    id , c1
    ------
    1 , 'ABC'
    5 , 'DEF'
    1 , 'XYZ'

    ( of course, if there is a unique index on id, that insert will fail due to duplicate key)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see

    the part that i didn't immediately understand was that the id column in this case was not defined as the primary key

    other databases require that the auto-incrementing number be the PK

    interesting behaviour in db2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm curious: What's the reason for such a requirement? Auto-generated columns and unique keys (primary keys) are orthogonal concepts, I would say. (Only the naming "AS IDENTITY" can be rather misleading.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i dunno, maybe ask the guys who wrote mysql and sql server

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by r937
    other databases require that the auto-incrementing number be the PK

    interesting behaviour in db2
    Hi,
    DB2 has two options:
    - generated always as identity
    - generated by default as identity
    Non of above two values requires primary key on 'identity' column.

    But if primary key is assigned (no problem at 'always' option) and 'default' option is used then in yanqinghuang (first post) the error message would be returned - duplicate primary key value.

    But if someone tries to inserts "INSERT INTO t1(c1) VALUES ('XYZ')" the same insert statement second time SQL is successful and gets value 2 in identity column. So auto-numbering goes on despite of first error message in exactly the same SQL.

    'Default' option is very useful if some rows are deleted from the table and export/import (with select * from...) will return exactly the same data back. But if using 'always' you can't import back identity column - so if some data was previously deleted then after export/import you get corrupted data in identity column.

    So I always prefer 'by default' option with primary key combination to get identity columns functionality.
    Hope this helps,
    Grofaty
    Last edited by grofaty; 11-08-07 at 09:18.

Posting Permissions

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