GENERATED ALWAYS AS IDENTITY



IBM's flagship relational database management system

GENERATED ALWAYS AS IDENTITY

Postby kk12345 » Sun Dec 26, 2010 6:40 pm

Dear Experts,

I have created a table EMP_IDENTITY as follows:

CREATE TABLE EMP_IDENTITY
(EMPID INTEGER GENERATED ALWAYS AS IDENTITY, NAME CHAR(20), DEPT INTEGER)
DB20000I The SQL command completed successfully.

DB2 INSERT INTO EMP_IDENTITY VALUES (1, 'KK', 20)
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0798N A value cannot be specified for column "EID" which is defined as GENERATED ALWAYS. SQLSTATE=428C9

DB2 INSERT INTO EMP_IDENTITY VALUES (1, 'KKK', 30)
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0117N The number of values assigned is not the same as the number of specified or implied columns or variables. SQLSTATE=42802

How to write the insert statement when GENERATED ALWAYS AS IDENTITY is used in the CRATE statement ???
kk12345
 
Posts: 18
Joined: Sun Dec 26, 2010 6:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: GENERATED ALWAYS AS IDENTITY

 

Re: GENERATED ALWAYS AS IDENTITY

Postby NicC » Sun Dec 26, 2010 9:46 pm

From the SQL Language Reference, which, of course, you have looked up:

CREATE TABLE EMPLOYEE
          (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
           NAME CHAR(30),
           SALARY DECIMAL(5,2),
           DEPTNO SMALLINT);

 INSERT INTO EMPLOYEE
          (NAME, SALARY, DEPTNO)
           VALUES (’Rupert’, 989.99, 50);
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: GENERATED ALWAYS AS IDENTITY

Postby kk12345 » Sun Dec 26, 2010 10:01 pm

Thanks NicC,
I got it.
kk12345
 
Posts: 18
Joined: Sun Dec 26, 2010 6:26 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post