I have "Assessment", "Attachment" and "Control" tables. Assessment and Attachment are many to many with a join table. Same with Control and Attachment are many to many with a join table. To minimize the manual insert/delete, I am using cascade="all-delete-orphan". If I have a attachment, say doc1, linked to both assessment and control. How do I remove the link between Assessment and Attachment, but still keep the link between the Control and Attachment? I tried to remove the Attachment object from the Assessment.getAttachments() collection and then persist the Assessment object. I got the org.hibernate.exception.ConstraintViolationException error because Hibernate tries to delete the Attachment record, even though the attachment is till linked to the a control.

Assessment mapping:

<set
name="attachements"
lazy="true"
cascade="all-delete-orphan"
table="ASSESSMENT_PERIOD_ATTACHEMENT"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
cascade="none"
table="ASSESSMENT_PERIOD_ATTACHEMENT"

@hibernate.collection-key
column="ASSESSMENT_ID"
@hibernate.collection-key
column="PERIOD"

@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
column="DOCUMENT_ID"
@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
column="PERIOD"
</meta>
<key>
<column name="ASSESSMENT_ID" />
<column name="PERIOD" />
</key>
<many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
>
<column name="DOCUMENT_ID" />
<formula>PERIOD</formula>
</many-to-many>
</set>

Control mapping:

<set
name="attachements"
lazy="true"
cascade="all-delete-orphan"
table="CONTROL_PERIOD_ATTACHEMENT"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
cascade="none"
table="CONTROL_PERIOD_ATTACHEMENT"

@hibernate.collection-key
column="CONTROL_INSTANCE_ID"
@hibernate.collection-key
column="PERIOD"

@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
column="DOCUMENT_ID"
@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
column="PERIOD"
</meta>
<key>
<column name="CONTROL_INSTANCE_ID" />
<column name="PERIOD" />
</key>
<many-to-many
class="com.gs.fw.sox404.rep.dto.Attachement"
>
<column name="DOCUMENT_ID" />
<formula>PERIOD</formula>
</many-to-many>
</set>

And the Attachment mapping:

<set
name="assessmentPeriods"
lazy="true"
cascade="none"
inverse="true"
table="ASSESSMENT_PERIOD_ATTACHEMENT"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
cascade="none"
table="ASSESSMENT_PERIOD_ATTACHEMENT"

@hibernate.collection-key
column="DOCUMENT_ID"
@hibernate.collection-key
column="PERIOD"

@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.AssessmentPeriod"
column="ASSESSMENT_ID"
@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.AssessmentPeriod"
column="PERIOD"
</meta>
<key>
<column name="DOCUMENT_ID" />
<column name="PERIOD" />
</key>
<many-to-many
class="com.gs.fw.sox404.rep.dto.AssessmentPeriod"
>
<column name="ASSESSMENT_ID" />
<formula>PERIOD</formula>
</many-to-many>
</set>
<set
name="controlInstancePeriods"
lazy="true"
cascade="none"
inverse="true"
table="CONTROL_PERIOD_ATTACHEMENT"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
cascade="none"
table="CONTROL_PERIOD_ATTACHEMENT"

@hibernate.collection-key
column="DOCUMENT_ID"
@hibernate.collection-key
column="PERIOD"

@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.ControlInstancePeriod"
column="CONTROL_INSTANCE_ID"
@hibernate.collection-many-to-many
class="com.gs.fw.sox404.rep.dto.ControlInstancePeriod"
column="PERIOD"
</meta>
<key>
<column name="DOCUMENT_ID" />
<column name="PERIOD" />
</key>
<many-to-many
class="com.gs.fw.sox404.rep.dto.ControlInstancePeriod"
>
<column name="CONTROL_INSTANCE_ID" />
<formula>PERIOD</formula>
</many-to-many>
</set>