Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Unanswered: How do I INSERT into a table that has an ID column that does not auto-increment?

    Hello,

    I work at a school and need to INSERT some locker data int our database. Specifically, into the 'locker' table.

    The locker table has the following columns:
    locker_id (integer)
    locker (varchar20)
    lock_serial (varchar20)
    locker_number(varchar20)
    campus_id (integer)
    location (varchar50)
    locker_position_id (integer)
    lock_type_id (integer)
    access_code (varchar50)
    staff_responsible (integer)
    notes (varchar250)
    status (integer)
    last_updated (timestamp)
    session_generator_id (integer)

    I don't want to insert data into every column, so my INSERT statement looks like this.

    INSERT INTO locker (locker_id, locker, locker_number, notes, location) VALUES (1, 'CM0','CM0', 'Breeze way Toilet right', 'CM105')
    go

    When run, I get this error: Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=612, COLNO=4" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.61.75

    I've done some googling, and I think my problem is something to do with the locker_id column. If I leave it out of my INSERT statement, I get the same error, so i'm pretty sure it's not setup to auto-increment. I've tried to just manually increment the locker_id throughout a series of connected INSERT statements, but I still get the same error. As far as I can tell I am not trying to insert a NULL value into a NOT NULL column.

    Any suggestions as to how I can get around this?

    Many thanks,
    Ben

  2. #2
    Join Date
    Sep 2013
    Posts
    6
    try 'db2look -d DBNAME -z SCHEMA -t locker -e', where DBNAME is your database name and SCHEMA is the schema table locker has, and put the output here.

  3. #3
    Join Date
    Aug 2013
    Posts
    6
    Quote Originally Posted by thuanqin View Post
    try 'db2look -d DBNAME -z SCHEMA -t locker -e', where DBNAME is your database name and SCHEMA is the schema table locker has, and put the output here.
    Hey, thanks for your response. Sorry - I am a bit of a noob. Very much an entry-level database operator.

    I have tried running the command you suggested but it is not recognised by the system.

    I tried running in Aqua Data Studio while connected to the relevant database and it told me the whole thing was 'an unexpected token'.

    Tried running in terminal while ssh'd into the database server and got a similar error.

    Would you mind being a bit more specific?

    Thanks again.

  4. #4
    Join Date
    Sep 2013
    Posts
    6
    Your problem should be caused by the error info db2 give:" Assignment of a NULL value to a NOT NULL",there is a column in your table that can not accept a null value. You can find this colume from this command:db2 select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=0". Or you can use command:"db2 describe table locker", and check if the COLUMN Nulls is 'YES'.


  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by thuanqin View Post
    db2 select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=0"
    You sure about that? Because the error message points to the column number 4:

    Code:
    Assignment of a NULL value to a NOT NULL column 
    "TBSPACEID=2, TABLEID=612, COLNO=4" is not allowed
    Not sure how the table definition was obtained, but there is a good chance that the column campus_id is not nullable.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2013
    Posts
    6
    Quote Originally Posted by thuanqin View Post
    Your problem should be caused by the error info db2 give:" Assignment of a NULL value to a NOT NULL",there is a column in your table that can not accept a null value. You can find this colume from this command:db2 select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=0". Or you can use command:"db2 describe table locker", and check if the COLUMN Nulls is 'YES'.

    OK - I ran this command: select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=0"

    The only column it returned was the 'locker_id' column. So that is definitely the column that doesn't accept NULL values.

    Does that help you to answer my original question at all? I'm still not able to INSERT INTO this table without getting the error in my original post, even though I am specifying a value for the locker_id. I'm pretty sure my insert statement needs to include something that auto-generates a new locker_id for each new row I am inserting, but I'm not sure how to do this.
    When I try and manually specify a value for locker_id, I still get the error.

    Thanks. I really appreciate your help.

  7. #7
    Join Date
    Sep 2013
    Posts
    6
    Sorry...you should type :db2 select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=4",just like n_i's suggestion.

  8. #8
    Join Date
    Aug 2013
    Posts
    6
    Quote Originally Posted by thuanqin View Post
    Sorry...you should type :db2 select "colname,colno from syscat.columns where tabname='LOCKER' and COLNO=4",just like n_i's suggestion.
    OK - output of that command:

    | | COLNAME | COLNO |
    | 1 | CAMPUS_ID | 4 |

  9. #9
    Join Date
    Sep 2013
    Posts
    6
    'CAMPUS_ID' need a value if you want to insert a row in this table. i guess this an id of a campus.

Posting Permissions

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