Foreign keys NOT created by SchemaUpdate nor SchemaExport

Linoux · **Joined:** Tue May 09, 2006 2:30 am **Posts:** 22

I there !

I got some problems with hbm2ddl tool.. I tried all modes (create, update, ...) and each time, my tables and primary keys are correctly created but no one foreign key !

And no errors are printed in hibernate log... Is there a property anywhere in configuration file to set to enable foreign keys creation ? What is I'm doing wrong ?

I'm using Hibernate annotations and Hibernate EntityManager under JBOSS 4, with PostrgreSQL 8.2.

Here is my datasource conf :

Code:

<datasources>
  <local-tx-datasource>
    <jndi-name>PostgresqlTest</jndi-name>
    <connection-url>jdbc:postgresql://127.0.0.1:5432/testdb</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <user-name>ogp</user-name>
    <password>ogpogp</password>    
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>    
  </local-tx-datasource>
</datasources>

And hibernate conf (persistence.xml)

Code:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
                   version="1.0">
   <persistence-unit name="hibernateEM" transaction-type="JTA">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <jta-data-source>java:/PostgresqlTest</jta-data-source>
      <properties>
         <property name="hibernate.dialect" value="org.hibernate.dialect.ProgressDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
      </properties>
   </persistence-unit>
</persistence>

My entities (without getters/setters which are of no importance here)

DbTestMessage.java :

Code:

@Entity
@Table(name = "test_message")
@javax.persistence.TableGenerator(name="DbTestMessage_GEN",
                                  table="id_sequence",
                                  pkColumnName = "entity_name",
                                  valueColumnName = "value",
                                  pkColumnValue="DbTestMessage",
                                  allocationSize=20)
public class DbTestMessage implements Serializable {
  private static final long serialVersionUID = 1L;
  @Id  @GeneratedValue(strategy = GenerationType.TABLE, generator="DbTestMessage_GEN")
  private Integer id;
  @Column(nullable = false, unique = false, name = "date")
  private Date date;
  @ManyToOne(optional = false)
  @JoinColumn(nullable = false, unique = false, name = "topic")
  private DbTestTopic topic;
  @Column(nullable = false, unique = false, name = "title")
  private String title;
  @Column(nullable = false, unique = false, name = "text")
  private String text;

  // Constructors, getters, setters
}

DbTestTopic.java :

Code:

@Entity
@Table(name = "test_topic")
@javax.persistence.TableGenerator(name="DbTestTopic_GEN",
                                  table="id_sequence",
                                  pkColumnName = "entity_name",
                                  valueColumnName = "value",
                                  pkColumnValue="DbTestTopic",
                                  allocationSize=20)
public class DbTestTopic implements Serializable {
  private static final long serialVersionUID = 1L;
  @Id  @GeneratedValue(strategy = GenerationType.TABLE, generator="DbTestTopic_GEN")
  private Integer id;
  @Column(nullable = false, unique = true, name = "name")
  private String name;
  @Column(nullable = true, unique = false, name = "nb_messages")
  private Integer nbMessages;

  // Constructors, getters, setters
}

Thanks a lot for any help !!

max · **Posted:** Wed Feb 21, 2007 5:29 am

what about showing the sql schemaexport/update generates?

Linoux · **Joined:** Tue May 09, 2006 2:30 am **Posts:** 22

Hi Max,

here is the interesting part of hibernate DEBUG logs while deploying my application (there is a few entities more than those I described before....) (hbm2ddl = update)

Code:

2007-02-21 INFO  [DatabaseMetadata] table not found: test_message
2007-02-21 INFO  [DatabaseMetadata] table not found: test_role
2007-02-21 INFO  [DatabaseMetadata] table not found: test_table
2007-02-21 INFO  [DatabaseMetadata] table not found: test_topic
2007-02-21 INFO  [DatabaseMetadata] table not found: test_user
2007-02-21 INFO  [DatabaseMetadata] table not found: test_user_role
2007-02-21 INFO  [DatabaseMetadata] table not found: test_message
2007-02-21 INFO  [DatabaseMetadata] table not found: test_role
2007-02-21 INFO  [DatabaseMetadata] table not found: test_table
2007-02-21 INFO  [DatabaseMetadata] table not found: test_topic
2007-02-21 INFO  [DatabaseMetadata] table not found: test_user
2007-02-21 INFO  [DatabaseMetadata] table not found: test_user_role
2007-02-21 INFO  [DatabaseMetadata] table not found: hibernate_unique_key
2007-02-21 INFO  [DatabaseMetadata] table not found: id_sequence
2007-02-21 DEBUG [SchemaUpdate] create table test_message (id integer not null, date timestamp not null, title varchar(255) not null, text varchar(255) not null, topic integer not null, primary key (id))
2007-02-21 DEBUG [SchemaUpdate] create table test_role (id integer not null, name varchar(255) not null, group_name varchar(255) not null, primary key (id))
2007-02-21 DEBUG [SchemaUpdate] create table test_table (id integer not null, name varchar(255) not null, date_field integer, primary key (id))
2007-02-21 DEBUG [SchemaUpdate] create table test_topic (id integer not null, name varchar(255) not null unique, nb_messages integer, primary key (id))
2007-02-21 DEBUG [SchemaUpdate] create table test_user (id integer not null, login varchar(255) not null, password varchar(255) not null, role_id integer, primary key (id))
2007-02-21 DEBUG [SchemaUpdate] create table test_user_role (user_id integer not null, role_id integer not null, primary key (user_id, role_id))
2007-02-21 DEBUG [SchemaUpdate] create table hibernate_unique_key ( next_hi integer )
2007-02-21 DEBUG [SchemaUpdate] insert into hibernate_unique_key values ( 0 )
2007-02-21 DEBUG [SchemaUpdate] create table id_sequence ( entity_name varchar(255),  value integer ) 
2007-02-21 INFO  [SchemaUpdate] schema update complete

It seems also that while on hbm2ddl=create-drop, while the foreign key was not generated, SchemaExport try to drop them when undeploying application. Such errors are visible only in DEBUG logs...

Thanks for your help !

hallmit · **Posted:** Wed Jan 13, 2010 11:59 am

(maybe so late but...someone else may looking for...)

I saw you're using ProgressDialect and this dialect does not support altering of tables (ProgressDialect.hasAlterTable() returns false).

The schema update or create tests this property for generating FK over all columns. See org.hibernate.cfg.Configuration methods generateSchemaCreationScript(...) or generateSchemaUpdateScript(...)

ps. I think you're mistaken in your persistence.xml there're an odd reference for PostgreSQL (and not Progress)
<jta-data-source>java:/PostgresqlTest</jta-data-source> (maybe you should wish use PostgreSQLDialect)