Hibernate version:
2.1.6
Mapping documents:

Parent table

<hibernate-mapping>
<class name="com.pojo.Company" table="companies">
<id name="id" type="int">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<set name="quotes" inverse="true" cascade="all-delete-orphan">
<key column="symbol_id"/>
<one-to-many class="com.pojo.Quote"/>
</set>
</class>
</hibernate-mapping>

Child table

<hibernate-mapping>
<class name="com.pojo.Quote" table="quotes">
<id name="id" type="int">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<many-to-one name="symbol" column="symbol_id" class="com.pojo.Company">
<column name="symbol" not-null="true" unique-key="INDX"/>
</many-to-one>
<property name="date" type="date">
<column name="date" not-null="true" unique-key="INDX"/>
</property>
</class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

NA, schema is not being created correctly

Full stack trace of any exception that occurs:

NA

Name and version of the database you are using:

MySQL

The generated SQL (show_sql=true):

create table quotes (
id integer not null auto_increment,
symbol_id integer,
date date not null,
primary key (id),
unique (date)
)

create table companies (
id integer not null auto_increment,
primary key (id)
)

alter table quotes add index FKC7789177E7BCF522 (symbol_id), add constraint FKC7789177E7BCF522 foreign key (symbol_id) references companies (id)


Debug level Hibernate log excerpt:

WARNING

I am new to Hibernate and have read the chapter on Parent/ Child relationships. I also have bought "Hibernate A Developers NoteBook" and read from front to back. I am puzzled with the issue....

I want to create a one-to-many relationship between 2 tables (table A the parent and table B the child). This works fine, but when I try to include the foreign key in a unqiue composite index with another column in table B the schema is not mapped correctly. Is my schema incorrect? How to create this?

Thanks in advance...

That is ... *very* wierd ....


are you 100% sure??

oh, its not strange - there is a bug in your mapping! You have defined the column on both the <many-to-one> element and on a nested <column> element. The <many-to-one> wins ;)

Okay, the <many-to-one> wins :), I can put and index on both the foreign key and another column with this schema...

<hibernate-mapping>
<class name="com.pojo.Quote" table="quotes">
<id name="id" type="int">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<many-to-one name="symbol" column="symbol_id" class="com.pojo.Company" index="IDX">
<property name="date" type="date">
<column name="date" not-null="true" index="IDX"/>
</property>
</class>
</hibernate-mapping>

which results in

create table quotes (
id integer not null auto_increment,
symbol_id integer,
date date not null,
primary key (id),
)

create index IDX on quotes (symbol_id, date)

How do I make the index unique? Meaning a stock symbol cannot have 2 quote entries for the same day.

unique-key

That would work great if <many-to-one> contained attribute "unique-key". The schema below is what I created and ran. Since, <many-to-one> does not have a "unique-key" attribute how do I tell hiberante to add the column "parent_id" to the unique constraint?

schema

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.pojo.Child" table="CHILD">
<id name="id" type="int" column="id">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<many-to-one name="parent" column="parent_id" class="com.pojo.Parent" index="IND"/>
<property name="col" type="int">
<column name="col" not-null="true" index="IND" unique-key=""/>
</property>
<property name="col2" type="int">
<column name="col2" not-null="true" index="IND" unique-key=""/>
</property>
</class>
</hibernate-mapping>

SQL

create table CHILD (
id integer generated by default as identity (start with 1),
parent_id integer,
col integer not null,
col2 integer not null,
unique (col, col2)
)

I finally understand what Gavin was trying to explain to me. I should remove the "column" attribute from the "many-to-one" mapping. It took some time to beat through this so I am adding the final xml to this topic trail to help other lost souls. Thanks Gavin for your help and patients :).

The many side of the relation

note the "column" attribute has been removed

<hibernate-mapping>
<class name="com.pojo.Child" table="CHILD">
<id name="id" type="int" column="id">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<many-to-one name="parent" class="com.pojo.Parent">
<column name="parent" index="ind" unique-key="unq"/>
</many-to-one>
<property name="col" type="int">
<column name="col" not-null="true" index="ind" unique-key="unq"/>
</property>
<property name="col2" type="int">
<column name="col2" not-null="true" index="ind" unique-key="unq"/>
</property>
</class>
</hibernate-mapping>

The one side of the relation

note the "column" attribute is defined here

<hibernate-mapping>
<class name="com.pojo.Parent" table="PARENT">
<id name="id" type="int" column="id">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<set name="children" inverse="true" cascade="all-delete-orphan">
<key column="parent_id"/>
<one-to-many class="com.pojo.Child"/>
</set>
</class>
</hibernate-mapping>

SQL output

create table CHILD (
id integer generated by default as identity (start with 1),
parent integer,
col integer not null,
col2 integer not null,
parent_id integer,
unique (parent, col, col2)
)

create index ind on CHILD (parent, col, col2)
alter table CHILD add constraint FK3D1FCFCC4AB08AA foreign key (parent) references PARENT

update child doc....


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.pojo.Child" table="CHILD">
<id name="id" type="int" column="id">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>
<many-to-one name="parent" class="com.pojo.Parent">
<column name="parent_id" index="ind" unique-key="unq"/>
</many-to-one>
<property name="col" type="int">
<column name="col" not-null="true" index="ind" unique-key="unq"/>
</property>
<property name="col2" type="int">
<column name="col2" not-null="true" index="ind" unique-key="unq"/>
</property>
</class>
</hibernate-mapping>