Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Bucharest, Romania
    Posts
    3

    Unanswered: DB2 Insert Problem.

    Hello there...

    Let me start by telling you that I am a complete newbie at DB2.

    I have the following table structure:

    Code:
    DROP TABLE IF EXISTS RIW.adr;
    CREATE TABLE RIW.adr (
            pk_adr  integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +20 , INCREMENT BY +1 , NO CACHE )
    ,adr_ref varchar(10)
    ,adr_no varchar(30)
    ,contact_name varchar(50)
    ,addr1 varchar(50)
    ,addr2 varchar(50)
    ,addr3 varchar(50)
    ,fk_state  integer
    ,county varchar(50)
    ,zip varchar(10)
    ,city varchar(50)
    ,fk_country  integer
    ,phone varchar(15)
    ,fax varchar(15)
    ,email varchar(50)
    ,www varchar(50)
    ,ainf clob
    ,ship_type  integer
    ,xtimestamp  timestamp
    ,xactive  integer  DEFAULT 1
     );
    ALTER TABLE RIW.adr ADD PRIMARY KEY (pk_adr)
    In this table, I have some 13000+ records.

    I am trying to insert another row in the table using this query:
    Code:
    INSERT INTO riw.adr ( pk_adr, fk_state,fk_country,ship_type,contact_name,adr_ref,adr_no,addr1,addr2,addr3,zip,county,city,phone,fax,email,www,ainf ) VALUES (DEFAULT, 1,1, DEFAULT,'31231',NULL, NULL, '3123123123',NULL, NULL, NULL, '233123',NULL, NULL, '312312312',NULL, NULL, NULL)
    BUT: it spits out the following error:

    <b>
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0803N One or more values in the INSERT statement, UPDATE statement, or
    foreign key update caused by a DELETE statement are not valid because the
    primary key, unique constraint or unique index identified by "1" constrains
    table "RIW.ADR" from having duplicate rows for those columns. SQLSTATE=23505
    </b>

    I am using PHP to connect to DB2. But in the command line, it gives me the same error...

    Can anybody help me with this?

    Thank you.

  2. #2
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    I only know DB2 z/OS (so I never uesd the command line), but to me it seems, that the key, you are just trying to insert is already in the table. Because your primary key columns was defined as IDENTITY BY DEFAULT, someone has put a value into this column when inserting a row. DB2 does not keep track about this, it only assures, that the values it computes, are unique.

    So make sure, that nobody puts a value in the primary key column.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Notice where it says '...unique index identified by "1" constrains table...' You can identify the unqique index that is causing the problem by executing the query supplied in the Messages and Codes manual for that error message (SQL0803N).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2004
    Location
    Bucharest, Romania
    Posts
    3
    Well...

    The other 13000+ rows that I was telling you about were all inserted with complete inserts like this one:
    Code:
    INSERT INTO RIW.adr ( pk_adr ,adr_ref ,adr_no ,contact_name ,addr1 ,addr2 ,addr3 ,fk_state ,county ,zip ,city ,fk_country ,
    phone ,fax ,email ,www ,ainf ,ship_type ,xtimestamp ,xactive  )
     VALUES ( 2 ,'ShipTo' ,NULL ,NULL ,'1120 W Magnolia treet' ,NULL ,NULL ,29 ,NULL ,'40210' ,'LOUISVILLE' ,1 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
    TIMESTAMP_FORMAT('2004-01-08 21:02:28', 'YYYY-MM-DD HH24:MI:SS') ,1  );
    Note that the primary key, pk_adr, is inserted with values.

    The insert I am trying to do now is not the same with the one above. It thies to insert the DEFAULT value into pk_adr.
    As far as I know, if one has a IDENTITY column (almost the same with auto_increment columns from mysql), one has to assign the DEFAULT value for that column on new inserts.

    Thanx anyway.
    Last edited by JohnDoe; 01-14-04 at 09:11.

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Well, it would have been better, if you had inserted all rows with DEFAULT. Now, because you didn't do that, the first value DB2 will asign is the start value, which is 20 (or may be 21).

    I don't know, if you can alter the start value. In DB2 V7 z/OS this is not possible. In this case, you have to insert 13000+ rows (always getting the duplicate message) or you have to drop and recreate your table with a higher start value.

  6. #6
    Join Date
    Jan 2004
    Location
    Bucharest, Romania
    Posts
    3
    You are so RIGHT....

    I have restarted the identity column from the max(column)+1 and everithing looks GOOD...


    Thank you very much...

  7. #7
    Join Date
    Jun 2010
    Posts
    4

    inserting new primary key problem

    I have got the same problem ... Can you please specify how did you changed the identity column.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    select max(PK) from <table name> to get the highest value and then
    alter table <table name> alter column <PK> restart with <next value>

Posting Permissions

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