Foreign key in compiste primary key (duplicated column error)

Question

We are just integrating Hibernate in our web application which has been developed since today using direct queries to a relational database. We have generated hbm.xml files in order to have all the entities and mappings implemented for accessing database via hibernate for reading and writing.

The thing is that our schema has many composite primary keys and, because of that, we have a lot of foreign keys refering to those composite ids.

Above you can see an example of database schema in which we are having many problems. There is also the hbm.xml file:

<class name="Profile" table="Profile" optimistic-lock="version">
    <id name="profileId" type "java.lang.Integer">
        <column name="profileId" />
        <generator class="assigned" />
    </id>
    <many-to-one name="location" class="Location" fetch="select" >
        <column name="locationId" length="3" not-null="true" />
    </many-to-one>
    <many-to-one name="users" class="Users" fetch="select"> 
          <column name="locationId" length="3" not-null="true" /> 
          <column name="userId" length="30" not-null="true" />
    </many-to-one>
     <many-to-one name="groupUsers" class="GroupUsers" fetch="select"> 
          <column name="locationId" length="3" not-null="true" /> 
          <column name="groupUserId" length="30" not-null="true" />
    </many-to-one>
</class>

<class name="Users" table="Users" optimistic-lock="version">
    <composite-id name="id" class="UsersId">
        <key-property name="locationId" type="string">
            <column name="locationId" length="3" />
        </key-property>
        <key-property name="userId" type="string">
            <column name="userId" length="30" />
        </key-property>
    </composite-id>

    <set name="profiles" table="Profile" inverse="true" lazy="true" fetch="select">
        <key>
            <column name="locationId" length="3" not-null="true" />
            <column name="userId" length="30" not-null="true" />
        </key>
        <one-to-many class="Profile" />
    </set>
</class>

<class name="GroupUsers" table="GroupUsers" optimistic-lock="version">
    <composite-id name="id" class="GroupUsersId">
        <key-property name="locationId" type="string">
            <column name="locationId" length="3" />
        </key-property>
        <key-property name="groupUserId" type="string">
            <column name="groupUserId" length="30" />
        </key-property>
    </composite-id>

    <set name="profiles" table="Profile" inverse="true" lazy="true" fetch="select">
        <key>
            <column name="locationId" length="3" not-null="true" />
            <column name="groupUserId" length="30" not-null="true" />
        </key>
        <one-to-many class="Profile" />
    </set>
</class>

<class name="Location" table="Location" optimistic-lock="version">
    <id name="locationId" type="string">
        <column name="locationId" length="3" />
        <generator class="assigned" />
    </id>

    <set name="profiles" table="Profile" inverse="true" lazy="true" fetch="select">
        <key>
            <column name="locationId" length="3" not-null="true" />
        </key>
        <one-to-many class="Profile" />
    </set>
</class>

When starting Tomcat I get the following error: "Profile column: locationId (should be mapped with insert="false" update="false")"

But if I try to put this two attributes in the many-to-one relation of the Profile.hbm.xml file, I can't insert a profile instance to the database (no effect):

<many-to-one name="location" class="Location" fetch="select" insert="false" update="false">
      <column name="locationId" length="3" not-null="true" />
</many-to-one>
 <many-to-one name="users" class="Users" fetch="select"insert="false" update="false"> 
      <column name="locationId" length="3" not-null="true" /> 
      <column name="userId" length="30" not-null="true" />
</many-to-one>
 <many-to-one name="groupUsers" class="GroupUsers" fetch="select" insert="false" update="false"> 
      <column name="locationId" length="3" not-null="true" /> 
      <column name="groupUserId" length="30" not-null="true" />
</many-to-one>

Can anyone help me, please?

Thank you very much in advance.

Why is `locationId` part of the primary key for Users and GroupUsers? I think that's the root of your problem - you're trying to use the same column as part of composite keys for multiple mappings. So why do it this way? Are you going to have multiple Users with the same `userId` who are only differentiated by their location? — dcsohl, Feb 06 '15 at 17:05
Try googling `create hbm.xml from sql error should be mapped with insert="false" update="false"`. Eg [How can I map “insert='false' update='false'” on a composite-id key-property which is also used in a one-to-many FK?](https://stackoverflow.com/q/4892925/3404097). — philipxy, Feb 07 '15 at 10:27

score 0 · Answer 1 · answered Feb 07 '15 at 13:57

Because you say you have many composite primary keys in a existing schema with data in it and you want to add hibernate to it. That sounds like you rather would have no composite primary keys.

Just a thought: I think it would not be such a big thing to drop your existing combined primary keys, make them uniqe indices instead and create new non combined primary keys updating all your data with a sequence (depending on the DMBS you are using).

That of course depends on how you want to integrate hibernate. There could be reasons of keeping the old combined primary keys. But maybeyou should think about it.

Foreign key in compiste primary key (duplicated column error)

1 Answers1