Howto insert into table with only a generated value?

benny84 · #1 (**permalink**) 04-17-10, 04:54

Hello everyone, I have a little trick question ;-)

I have the following table:

Quote:

CREATE TABLE RoutingRoute (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, PRIMARY KEY(id))

How do i insert into this table?

Quote:

INSERT INTO RoutingRoute (id) VALUES (null)

I get the following error message:

Quote:

PDOException: SQLSTATE[23502]: Not null violation: -407 [IBM][CLI Driver][DB2/LINUX] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=6, COLNO=0" is not allowed. SQLSTATE=23502

Stealth_DBA · #2 (**permalink**) 04-17-10, 08:18

Benny84, If you want DB2 to generate the value use VALUES(DEFAULT). If you want to supply the value (since you defined the column as GENERATED BY DEFAULT), use VALUES(integer-value) (ex. VALUES(2)).

VALUES(NULL) is giving you an error because you defined the column as NOT NULL.

DB2Plus · #3 (**permalink**) 04-17-10, 12:00

I see the problem if you have only one GENERATED BY DEFAULT AS IDENTITY column in the table to insert columns by values in this table.

Adding the dummy column in this table could make your life easier.

Kara

stolze · #4 (**permalink**) 04-19-10, 07:49

No dummy column is needed. Just use the DEFAULT keyword for GENERATED columns. (It doesn't matter if it is a GENERATED ALWAYS or GENERATED BY DEFAULT column.)

Marcus_A · #5 (**permalink**) 04-19-10, 09:40

Quote:

Originally Posted by stolze

No dummy column is needed. Just use the DEFAULT keyword for GENERATED columns. (It doesn't matter if it is a GENERATED ALWAYS or GENERATED BY DEFAULT column.)

Stealth_DBA already answered the question in a post above:

"Benny84, If you want DB2 to generate the value use VALUES(DEFAULT)."

Lenny77 · #6 (**permalink**) 04-19-10, 11:15

Quote:

Originally Posted by benny84

Hello everyone, I have a little trick question ;-)

I have the following table:

How do i insert into this table?

I get the following error message:

For me this one is not too bad:

Code:

insert into RoutingRoute
select ifnull(max(id), 0) + 1
  from RoutingRoute

Lenny

Lenny77 · #7 (**permalink**) 04-21-10, 12:13

You have to use DEFAULT instead of NULL:

Code:

INSERT INTO RoutingRoute (id) VALUES (DEFAULT); 
INSERT INTO RoutingRoute (id) VALUES (DEFAULT); 
INSERT INTO RoutingRoute (id) VALUES (DEFAULT); 

select * from RoutingRoute

Result:

Quote:

ID
1
2
3

Lenny

karthik.try20 · #8 (**permalink**) 04-22-10, 08:50

Hi Lenny,

Nice solution

By
KK

Internet Services

Web Development

Digital Marketing

Consumer Tech