Thursday, November 19, 2009

Join (association) Table

one-to-many can be mapped with join table as many-to-many (unique="true"), it gives some flexibility that many side doesn't need to have FK to one side, hence they are more loosely coupled. The relation is kept in a separated table. E.g., both Template and Task have one-to-many relationship with DeployWlc. Then we can keep DeployWlc table intact and have separated join tables ce_template_deployWlc, and ce_task_deployWlc.


<class name="Person">
<set name="addresses">
<key column="personId" not-null="true"/>
<one-to-many class="Address"/>
</set>
</class>

<class name="Person">
<set name="addresses" table="PersonAddress">
<key column="personId"/>
<many-to-many column="addressId"
unique="true"
class="Address"/>
</set>
</class>

For many-to-one, we can also have join table, so that on database table, many side doesn't have FK column, but on java object, it has the reference. If both side defines the join table, it creates a bidirectional one-to-many/many-to-one association using a join table.

<many-to-one name="person" class="Person" column="personId"/>


<key column="addressId" unique="true"/>
<many-to-one name="person" class="Person" column="personId"/>


A cascade myth: the CASCADE on collection is not for join(association) table, but for the base table on the other side. The data entries on association table are updated without cascade setting.

The association could be strong or weak. For strong association, define Hibernate cascade "all, delete-orphan" will do it; For weak association, we may run into some issues. For example, the bidirectional association table, the *inverse* side may have FK constraint violation issue. E.g., ApGroup(inverse) <--> Ap, the deletion of ApGroup will fail since it is referenced by association table. It is OK for Ap deletion which is taken care of by Hibernate. So the solution is add database level "on delete cascade" for FK constraint tbl_apGroup_ap -> apGroup.

alter table tbl_apGroup_ap add index FK4F9906CCF1F23ED3 (apGroupId), add constraint FK4F9906CCF1F23ED3 foreign key (apGroupId) references tbl_apGroup (id) on delete cascade;

It means the deletion on parent table (pointed) tbl_apGroup will cascade the deletion on child table tbl_apGroup_ap.

one-to-one is mapped with many-to-one (unique="true") with foreign key association. If using one-to-one, it is using primary key association.