Topic
  • 1 reply
  • Latest Post - ‏2010-04-17T12:12:15Z by SystemAdmin
beberlei
beberlei
1 Post

Pinned topic Howto insert into table with only a generated value?

‏2010-04-17T08:59:04Z |
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: SQLSTATE23502: Not null violation: -407 IBMCLI DriverDB2/LINUX SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=6, COLNO=0" is not allowed. SQLSTATE=23502
Updated on 2010-04-17T12:12:15Z at 2010-04-17T12:12:15Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Howto insert into table with only a generated value?

    ‏2010-04-17T12:12:15Z  
    beberlei, you are having a problem because you defined the column as NOT NULL and then try to Insert a NULL. Since you defined the column as GENERATED BY DEFAULT, you can Insert with a Default value or a Supplied value:
    
    CREATE TABLE RoutingRoute (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, PRIMARY KEY(id)) DB20000I  The SQL command completed successfully.   INSERT INTO RoutingRoute (id) VALUES (
    
    null) DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0407N  Assignment of a NULL value to a NOT NULL column 
    "TBSPACEID=2,  TABLEID=13, COLNO=0
    " is not allowed.  SQLSTATE=23502   SELECT * FROM RoutingRoute   ID -----------   0 record(s) selected.     INSERT INTO RoutingRoute (id) VALUES (
    
    default) DB20000I  The SQL command completed successfully.   SELECT * FROM RoutingRoute   ID ----------- 1   1 record(s) selected.     INSERT INTO RoutingRoute (id) VALUES (100) DB20000I  The SQL command completed successfully.   SELECT * FROM RoutingRoute   ID ----------- 1 100   2 record(s) selected.