hibernate use two foreign keys for different coloumns?

Question

is it possible to map two foreign keys linked to the same table? its best i show you what i mean.

i have a table called fixtures which currently looks like this:

<class name="" table="">
    <id name="id" column="id">
        <generator class="Increment" />
    </id>
    <property name="date" type="" column="" />
    <property name="" type="" column="" />
    <many-to-one name="awayTeam" column="teamId" not-null="true" />
    <many-to-one name="homeTeam" column="teamId" not-null="true" />
</class>

And i have another table called Teams which holds the teams data.

<class name="" table="">
    <id name="teamId" column="id">
        <generator class="Increment" />
    </id>
    <property name="name" type="String" column="name" />
    <property name="fixturesUrl" type="String" column="fixturesUrl" />
    <property name="rssNewsUrl" type="String" column="rssNewsUrl" />
</class>

in a match/fixture, each match has two teams, an away and home team that i want it to map and linked in a one to many relaltionship with my teams table. is that possible in hibernate? if so, how? what am i missing in my code?

Is their also a way to say each fixture record cannot have same teams playing eachother i.e. away and home team has to be unique?

cheers in advance

Answer 1

is it possible to map two foreign keys linked to the same table?

Yes. What you have should work. If it doesn't, please explain the problem.

Is their also a way to say each fixture record cannot have same teams playing eachother i.e. away and home team has to be unique?

This should be doable using the properties element. From the documentation:

5.1.16. Properties

The <properties> element allows the definition of a named, logical grouping of the properties of a class. The most important use of the construct is that it allows a combination of properties to be the target of a property-ref. It is also a convenient way to define a multi-column unique constraint.

See also this previous question for more details.

---

Update: I'm not sure why but I couldn't get the unique key generated using the <properties> element (maybe I misused it, I didn't try too long) but here is a solution using a <natural-id> element.

The mapping of Team:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="com.stackoverflow.q4089539.Team" table="TEAM">
    <id name="id" type="java.lang.Long">
      <column name="ID" />
      <generator class="native" />
    </id>
    <property name="name" type="java.lang.String">
      <column name="NAME" />
    </property>
    <property name="fixturesUrl" type="java.lang.String">
      <column name="FIXTURESURL" />
    </property>
    <property name="rssNewsUrl" type="java.lang.String">
      <column name="RSSNEWSURL" />
    </property>
  </class>
</hibernate-mapping>

And the Fixture:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="com.stackoverflow.q4089539.Fixture" table="FIXTURE">
    <id name="id" type="java.lang.Long">
      <column name="ID" />
      <generator class="native" />
    </id>
    <natural-id mutable="false">
      <many-to-one class="com.stackoverflow.q4089539.Team" name="awayTeam" not-null="true">
        <column name="AWAYTEAM"/>
      </many-to-one>
      <many-to-one class="com.stackoverflow.q4089539.Team" name="homeTeam" not-null="true">
        <column name="HOMETEAM" />
      </many-to-one>
    </natural-id>
    <property generated="never" lazy="false" name="date" type="java.util.Date">
      <column name="DATE" />
    </property>
  </class>
</hibernate-mapping>

And here is the generated DDL:

create table FIXTURE (
    ID bigint not null,
    AWAYTEAM bigint not null,
    HOMETEAM bigint not null,
    DATE timestamp,
    primary key (ID),
    unique (AWAYTEAM, HOMETEAM)
)

create table TEAM (
    ID bigint not null,
    NAME varchar(255),
    FIXTURESURL varchar(255),
    RSSNEWSURL varchar(255),
    primary key (ID)
)

alter table FIXTURE 
    add constraint FKF88585E9445D9A98 
    foreign key (AWAYTEAM) 
    references TEAM

alter table FIXTURE 
    add constraint FKF88585E987B44C09 
    foreign key (HOMETEAM) 
    references TEAM