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 !

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

that's weird

what happened to the ABC row? it gets rejected?

"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)

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

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.)

i dunno, maybe ask the guys who wrote mysql and sql server

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