DB2 Insert Problem.

JohnDoe · #1 (**permalink**) 01-14-04, 07:17

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.

Walter Janissen · #2 (**permalink**) 01-14-04, 07:51

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.

Marcus_A · #3 (**permalink**) 01-14-04, 07:54

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).

JohnDoe · #4 (**permalink**) 01-14-04, 07:57

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.

Walter Janissen · #5 (**permalink**) 01-14-04, 08:04

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.