Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    1

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

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

    I have the following table:

    CREATE TABLE RoutingRoute (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, PRIMARY KEY(id))
    How do i insert into this table?

    INSERT INTO RoutingRoute (id) VALUES (null)
    I get the following error message:

    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

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze View Post
    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)."
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Maybe ?

    Quote Originally Posted by benny84 View Post
    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

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up complete solution of the problem

    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:

    ID
    1
    2
    3
    Lenny

  8. #8
    Join Date
    Apr 2010
    Location
    Chennai
    Posts
    4
    Hi Lenny,

    Nice solution

    By
    KK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •