Need help with DuplicateKeyException

[Dhananjay Inamdar]

Hello All,

I have following 4 tables and respective 4 mapping files.

1) Project

TABLLE
=====
CREATE TABLE "DB2INST1"."PROJECTS" (
"PROJECT_ID" BIGINT NOT NULL ,
"USER_ID" BIGINT ,
"PALETTE_ID" BIGINT ,
"NAME" VARCHAR(64) )
ALTER TABLE "DB2INST1"."PROJECTS"
ADD PRIMARY KEY
("PROJECT_ID");

ALTER TABLE "DB2INST1"."PROJECTS"
ADD CONSTRAINT "SQL070320001132660" FOREIGN KEY
("USER_ID")
REFERENCES "DB2INST1"."USERS"
("USER_ID")
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

MAPPING
=======<class
name="com.ed2.model.Project"
table="PROJECTS"
lazy="false"
>

<id
name="projectId"
type="java.lang.Long"
column="PROJECT_ID"
>

<generator class="increment" />
</id>

<many-to-one
name="user"
class="com.ed2.model.User"
not-null="true"
>
<column name="USER_ID" />
</many-to-one>

<property
name="paletteId"
type="java.lang.Long"
column="PALETTE_ID"
length="19"
/>

<property
name="name"
type="java.lang.String"
column="NAME"
length="64"
/>
</class>


2) IMAGE
TABLE
===== CREATE TABLE "DB2INST1"."IMAGE" (
"IMAGE_ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +9223372036854775807
NO CYCLE
NO CACHE
NO ORDER ) ,
"NAME" VARCHAR(80) NOT NULL ,
"PROJECT_ID" BIGINT NOT NULL)

ALTER TABLE "DB2INST1"."IMAGE"
ADD PRIMARY KEY
("IMAGE_ID");

ALTER TABLE "DB2INST1"."IMAGE"
ADD CONSTRAINT "SQL070320001132270" FOREIGN KEY
("PROJECT_ID")
REFERENCES "DB2INST1"."PROJECTS"
("PROJECT_ID")
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

MAPPING
======
<class
name="com.ed2.model.PypImage"
table="PYP_IMAGE"
lazy="false"
>

<id
name="pypImageId"
type="java.lang.Long"
column="PYP_IMAGE_ID"
>

<generator class="native" />
</id>

<property
name="name"
type="java.lang.String"
column="NAME"
not-null="true"
length="80"
/>

<many-to-one
name="project"
class="com.ed2.model.Project"
not-null="true"
>
<column name="PROJECT_ID" />
</many-to-one>

</class>


3) PALETTE

CREATE TABLE "DB2INST1"."PALETTES" (
"PALETTE_ID" BIGINT NOT NULL ,
"USER_ID" BIGINT ,
"NAME" VARCHAR(64) ,
"PALETTE_TYPE" INTEGER WITH DEFAULT 0 )

ALTER TABLE "DB2INST1"."PALETTES"
ADD PRIMARY KEY
("PALETTE_ID");

MAPPING
======
<class name="com.ed2.model.Palette" table="PALETTES" lazy="false">
<id name="palleteId" type="java.lang.Long"
column="PALETTE_ID">
<generator class="increment" />
</id>

<property name="userId" type="java.lang.Long" column="USER_ID"
length="19" />
<property name="name" type="java.lang.String" column="NAME"
length="64" />
<property name="palette_type" type="java.lang.Integer"
column="PALETTE_TYPE" length="10" />

</class>

4) PALETTE_COLORS

TABLE
====CREATE TABLE "DB2INST1"."PALETTE_COLOR" (
"PALETTE_ID" BIGINT NOT NULL ,
"COLOR_ID" VARCHAR(16) ,
"ORDER" INTEGER )

CREATE INDEX "DB2INST1"."PAL_COLOR_PALID" ON "DB2INST1"."PALETTE_COLOR"
("PALETTE_ID" ASC) PCTFREE 10 CLUSTER MINPCTUSED 10
ALLOW REVERSE SCANS;


MAPPING
======
<class name="com.ed2.model.PaletteColor"
table="PALETTE_COLOR" lazy="false">
<composite-id>
<key-property name="palleteId" type="java.lang.Long"
column="PALETTE_ID" />
<key-property name="colorId" type="java.lang.String"
column="COLOR_ID" />
<key-property name="order" type="java.lang.Integer"
column="ORDER" />
</composite-id>
</class>



I am using Spring-Hibernate combination in my J2EE application. In individual DAO classes for all these tables I am calling either Save() or update() method, depends on the scenario like

getHibernateTemplate().save(palette);  

//For save first time

getHibernateTemplate().update(palette); 

//For update object


Not everytime but sometime I get following error when ever I am saving all these objects in database


[3/28/07 9:20:42:099 PDT] 6d3b824c AbstractFlush E org.hibernate.event.def.AbstractFlushingEventListener Could not synchronize database state with session[3/28/07 9:20:42:100 PDT] 6d3b824c AbstractFlush E org.hibernate.event.def.AbstractFlushingEventListener TRAS0014I: The following exception was logged org.hibernate.exception.ConstraintViolationException: could not insert: [com.ed2.model.Palette]at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
....
...
..
...
...
..
Caused by: com.ibm.websphere.ce.cm.DuplicateKeyException: 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 "DB2INST1.PALETTES" from having duplicate rows for those columns.
...
...
...
org.hibernate.exception.ConstraintViolationException: could not insert: com.ed2.model.Palette



I am printing value of Palette_ID just before calling save method and it shows as null, as it should be, as this is a new obejct which I want to save in database.

I am not sure what is going wrong and why I am getting this error. I would like to know if you have any solution for this problem.

Please let me know if you need any further info. about these 4 tables or mappings.

Thanks in advance!