SchemaExport Task doesnt generate proper column definitions for Foreign Keys.

Hi ,

I am new to Hibernate, and using Hibernate 3.1 beta 2. The following is my mapping document. I have used the org.hibernate.tool.hbm2ddl.SchemaExportTask

************************************* START HIBERNATE MAPPING *************************************
<?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="Class1" table="Table1" lazy="true">
<id name="id" type="long" column="ID" >
<generator class="native" />
</id>
<property name="siteId" type="string" column="SITE_ID" not-null="true" unique="true" length="2" />
<property name="siteName" type="string" column="SITE_NAME" not-null="true" length="30" />
<property name="siteNumber" type="int" column="SITE_NO" not-null="true" unique="true" length="3" />
<property name="siteType" type="char" column="SITE_TYPE" not-null="true" />
<property name="siteDbLink" type="string" column="SITE_DBLINK" not-null="true" length="50" />
<property name="isFunctional" type="char" column="FUNCTIONAL_YN" not-null="true" />
</class>

<class name="Class2" table="Table2" lazy="true">
<id name="id" type="long" column="ID" >
<generator class="native" />
</id>
<many-to-one name="siteId" column="SITE_ID" class="Class1" property-ref="siteId" lazy="proxy"/>
<property name="workstationBased" type="char" column="WS_BY_IP_NAME" not-null="true" />
<property name="changeWorkstation" type="char" column="CHANGE_WS_AT_FCY_YN" not-null="true" />
</class>

</hibernate-mapping>
************************************* END HIBERNATE MAPPING *************************************

Table 1 has a not null unique key referenced by Table 2, by a many-to-one mapping.

The following is the schema generated (I have done for both MySQl and HSQLDB), where the SITE_ID in Table2 is created to be varchar(255) instead of varchar(2)

************************************* START GENERATED SCHEMA EXPORT MYSQL *************************************
alter table Table2
drop
foreign key FK94DC2A44F59FEAEC;

drop table if exists Table1;

drop table if exists Table2;

create table Table1 (
ID bigint not null auto_increment,
SITE_ID varchar(2) not null unique,
SITE_NAME varchar(30) not null,
SITE_NO integer not null unique,
SITE_TYPE char(1) not null,
SITE_DBLINK varchar(50) not null,
FUNCTIONAL_YN char(1) not null,
primary key (ID)
) type=InnoDB;

create table Table2 (
ID bigint not null auto_increment,
SITE_ID varchar(255),
WS_BY_IP_NAME char(1) not null,
CHANGE_WS_AT_FCY_YN char(1) not null,
primary key (ID)
) type=InnoDB;

alter table Table2
add index FK94DC2A44F59FEAEC (SITE_ID),
add constraint FK94DC2A44F59FEAEC
foreign key (SITE_ID)
references Table1 (SITE_ID);

************************************* END GENERATED SCHEMA EXPORT MYSQL *************************************

************************************* START GENERATED SCHEMA EXPORT HSQLDB *************************************
alter table Table2
drop constraint FK94DC2A44F59FEAEC;

drop table Table1 if exists;

drop table Table2 if exists;

create table Table1 (
ID bigint generated by default as identity (start with 1),
SITE_ID varchar(2) not null,
SITE_NAME varchar(30) not null,
SITE_NO integer not null,
SITE_TYPE char(1) not null,
SITE_DBLINK varchar(50) not null,
FUNCTIONAL_YN char(1) not null,
primary key (ID),
unique (SITE_NO),
unique (SITE_ID)
);

create table Table2 (
ID bigint generated by default as identity (start with 1),
SITE_ID varchar(255),
WS_BY_IP_NAME char(1) not null,
CHANGE_WS_AT_FCY_YN char(1) not null,
primary key (ID)
);

alter table Table2
add constraint FK94DC2A44F59FEAEC
foreign key (SITE_ID)
references Table1 (SITE_ID);

************************************* END GENERATED SCHEMA EXPORT HSQLDB *************************************

Am I doing anything wrong ?? Any help is greatly appreciated.

Thanks in Advance
ChandraSekhar

try and set the length on the many-to-one too

Length is not a valid attribute in a many-to-one, however adding an explicit column definition did the job.

Thanks
ChandraSekhar