EJB3 / hibernate and foreign keys in mysql

cdelarue · **Joined:** Fri Jan 30, 2009 12:08 pm **Posts:** 3

I am learning EJB3 and I met some problems with hibernate with constraint in mysql on foreign key, in the generated sql.

I have two entities, User et Car with a OneToOne on User.fashionCar. The connection mode is create-drop :

Code:

The Entities :

Code:

@Entity
@Table(name="USER")
public class User implements Serializable
{
    ... id ...
    protected Car fashionCar;
   @OneToOne
    public Car getFashionCar() {
        return fashionCar;
    }
    public void setFashionCar(Car fashionCar) {
        this.fashionCar = fashionCar;
    }
}

@Entity
@Table(name="CAR")
public class Car implements Serializable
{
  ... id ...
} 

The sql to generate the table (from the server.log)

Code:

    create table .USER (
        id bigint not null auto_increment,
        fashionCar_id bigint,
        primary key (id)
    )

    create table .CAR (
        id bigint not null auto_increment,
         primary key (id)
    ) 

The . usage before the name of the table make sens to MySql, the schema defined in the JDBC connection is used :

Code:

jdbc:mysql://localhost:3306/test

When the constraint for the foreign key (or index) is produced :

Code:

  alter table .USER
        add index FK27E3CB3C5F8B7E (fashionCar_id),
        add constraint FK27E3CB3C5F8B7E
        foreign key (fashionCar_id)
        references .CAR (id)

2009-03-31 12:14:09,897 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: alter table .USER add index FK27E3CB3C5F8B7E (fashionCar_id), add constraint FK27E3CB3C5F8B7E foreign key (fashionCar_id) references .CAR (id)

2009-03-31 12:14:09,897 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Can't create table 'test.#sql-728_1' (errno: 150)

I understand the Can't create table`test.#sql-728_1` says that the MySql code is not good ... I guess.

If I add the schema in the entities

Code:

@Entity
@Table(name="USER", schema="test")
public class User implements Serializable
{
... 

or in the sql code, the code is valid :

Code:

   alter table test.USER
        add index FK27E3CBB4701D40 (secondCar),
        add constraint FK27E3CBB4701D40
        foreign key (secondCar)
        references test.CAR (id)

Any suggestions ?

mmerder · **Posted:** Fri Apr 03, 2009 8:46 am

did you configure a default schema somewhere in your configuration?

cdelarue · **Joined:** Fri Jan 30, 2009 12:08 pm **Posts:** 3

As far as I know, the only place where I set a schema was in the jdbc connection :

Code:

jdbc:mysql://localhost:3306/test

Is there any other place where a schema can be set ?

mmerder · **Posted:** Sat Apr 04, 2009 7:17 am

You can set a property in your config to set the default schema: "hibernate.default_schema"