[hibernate-issues] [Hibernate-JIRA] Resolved: (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.

[Diego Pires Plentz (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2679?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Diego Pires Plentz resolved HHH-2679.
-------------------------------------

      Assignee: Diego Pires Plentz
    Resolution: Duplicate

> 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: Darth
>            Assignee: Diego Pires Plentz
>            Priority: Critical
>   Original Estimate: 2 weeks
>  Remaining Estimate: 2 weeks
>
> 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