[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2679) One to Many Unidirectional Association using Join Tables and <list> causes duplicate key constraint violation when an entry is deleted from the list.

[Priyajeet Hora (JIRA)]

One to Many Unidirectional Association using Join Tables and <list> causes duplicate key constraint violation when an entry is deleted from the list.
-----------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: HHH-2679
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2679
             Project: Hibernate3
          Issue Type: Bug
          Components: core
    Affects Versions: 3.2.2
         Environment: Java 1.5, Hibernate 3.2, MySQL 5. Might also effect NHibernate similarly.
            Reporter: Priyajeet Hora
            Priority: Critical


Consider this unidirectional one-to-many relation done using join tables. 
Person --> Address (*). 

The hibernate mapping is pretty much the same as 
http://www.hibernate.org/hib_docs/v3/reference/en/html/associations.html#assoc-unidirectional-join-12m 
Though I am using <list> rather than a <set> 

Following is the DDL (presented roughly) created by the hbm2ddl tool. 

Table PersonAddress 

Person_id varcahr not null 
Address_id varchar not null unique 
Person_list_index integer not null 
primary key (Person_id, Person_list_index) 

I create a person1 - person_id=1. 
I create add1, add2, add3 - address_id=2,3,4 respectively. 
then I add all 3 addresses to the list in person1. 
persist it to database. 
all good. 

At this point in the database --

person_id---person_list_index---address_id 
1--0--2 
1--1--3 
1--2--4


I get person1 back. 
I remove add1. 
save person1 back. 
CONSTRIANT VIOLATION HERE DUE TO DUPLICATE KEY. 

looking at what hibernate does ... 

So after 1st save and before remove this is whats in the database - 

person_id---person_list_index---address_id 
1--0--2 
1--1--3 
1--2--4 

basically shows the one to many there. 

I want to remove the record no. 1--0--2. That is the 1st one. 
So with the 1st row gone, hibernate needs to shift other records up so that the new list index is assingned. 

expected output---- 

1--0--3 
1--1--4 

This is how hibernate proceeds 
Step1: save info and delete on 1--2--4, to get rid of that last record. 
Step2: update 1--0--2 to 1--0--3 

BANG...CONSTRAINT VIOLATION DUE TO DUPLICATE KEY.
Why? because "3" in that address_id column is already there and that column is "unique" according to DDL. 

Step3: update 1--0--3 to info saved from step1, that is to 1--1--4. 

But cant persist this since step 2 fails. 
Basically the algorithm of copy the value up when a delete is performed is not doing the copy correctly.
Way to get around this is to remove the "unique" constrint, but then that makes the relation a many to many.

-----------------------------------------------------------------------------

CREATE TABLE `PERSON`
(
	`PRESON_ID` varchar(255) NOT NULL, PRIMARY KEY (PRESON_ID) 
)


CREATE TABLE `ADDRESS`
(
	`ADDRESS_ID` varchar(255) NOT NULL, PRIMARY KEY (ADDRESS_ID) 
)


CREATE TABLE `PERSONADDRESS`
(
	`PERSON_ID` varchar(255) NOT NULL 
	, `PERSON_IDX` integer  NOT NULL
	, `ADDRESS_ID` varchar(255) NOT NULL UNIQUE, PRIMARY KEY (PERSON_ID, PERSON_IDX)
)

ALTER TABLE `PERSONADDRESS`
ADD INDEX `PERSONADDRESS_FK_PERSON` (`PERSON_ID`),
ADD CONSTRAINT `PERSONADDRESS_FK_PERSON`
FOREIGN KEY (`PERSON_ID`)
REFERENCES PERSON(`PERSON_ID`)


ALTER TABLE `PERSONADDRESS`
ADD INDEX `PERSONADDRESS_FK_ADDRESS` (`ADDRESS_ID`),
ADD CONSTRAINT `PERSONADDRESS_FK_ADDRESS`
FOREIGN KEY (`ADDRESS_ID`)
REFERENCES ADDRESS(`ADDRESS_ID`)


Person.hbm.xml
-------------------------
<list name="AddressList" lazy="false" table="PersonAddress">
	<key column="PERSON_ID" />
	<index column="PERSON_IDX" />
	<many-to-many column="ADDRESS_ID" unique="true" class="Address" />
</list>

Address.hbm.xml
--------------------------
No information about Person, since this is a unidirectional association.
Target doesnt know about the source.


Test psuedocode
-------------------------
//start transaction
Person person = new Person();
Address add1 = new Address();
Address add2 = new Address();
Address add3 = new Address();

person.add(add1);
person.add(add2);
person.add(add3);

// persist to database
person.save
// end transaction


// start transaction
// get back that person from database
person.remove(add1);

// persist to database
person.save <<<<<<<<<<<<<<< FAILS, duplicate key constraint violation
// end transaction

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira