|
View previous topic :: View next topic
|
| Author |
Message |
mohan_eluri
New User
Joined: 14 Mar 2006
Posts: 2
Location: Hyderabad
|
|
|
|
Can any body explain the difference between 'GENERATED BY DEFAULT AS IDENTITY' and 'GENERATED ALWAYS AS IDENTITY' ?
Let suppose i've a table declaration like
CREATE TABLE t1
(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
c1 CHAR(10) NOT NULL,
c2 CHAR(10)
INSERT INTO t1 VALUES (1, 'abc', NULL);
Will the insert operation pass, if not why?
Best regards,
Mohan Eluri. |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
The GENERATED ALWAYS AS IDENTITY clause indicates that DB2 will always generate a unique value for the identity column for each row inserted into the table ....
DB2 will issue an error if a value is specified n an insert operation for the identity column defined using this clause ......
GENERATED BY DEFAULT AS IDENTITY clause will cause DB2 to generate unique values for the identity column during insert operations if no value is specified for the identity column. However, if a value is specified for the identity column, DB2 will use it in the insert operation ....
Now i hope you know what you can expect out of your insert stmt .... |
|
| Back to top |
|
|
feng hao
New User
Joined: 26 Mar 2008
Posts: 44
Location: China
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
GENERATED BY DEFAULT clause is meant for use for data propagation
where:
the intent is to copy the contents of an existing table
or
for the unload and reloading of a table.
If a value is in the data to load - use it, otherwise db2 will generate a value. |
|
| Back to top |
|
|
feng hao
New User
Joined: 26 Mar 2008
Posts: 44
Location: China
|
|
|
|
Thanks very much, Dick!
But there is one thing which is still troubling me.
"generated by default" allows users to assign a number for the column by themselves, meanwhile it only remembers what it has generated rather than the newest number in the column.
So I wonder what is the purpose when we writed "generated by default as identity". Actually what we want is "generated always as identity". |
|
| Back to top |
|
|
|
|
