[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4595) DDL export generates FK constraint before the essential unique constraint, and fails

[Chris Wilson (JIRA)]

DDL export generates FK constraint before the essential unique constraint, and fails
------------------------------------------------------------------------------------

                 Key: HHH-4595
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4595
             Project: Hibernate Core
          Issue Type: Bug
          Components: annotations
    Affects Versions: 3.3.2
         Environment: Core 3.3.2, with Annotations 3.4.0, on MySQL 5.0.51a-3ubuntu5.4.
            Reporter: Chris Wilson
         Attachments: HibernateJoinPropertyRefMultipleColumnsFails.java

With the attached test case, which defines some classes and tries to generate the DDL for them, Hibernate tries to generate the foreign key constraint before the unique constraint:

create table user_roles (id integer not null, role varchar(255), user_name varchar(255), project_id integer, site_id integer not null, primary key (id))
...
alter table user_roles add constraint FK73429949AACF5CB1 foreign key (project_id, site_id) references project_site (project_id, site_id)

MySQL doesn't allow this, generating the following error:

"Constraint 'FK73429949AACF5CB1' is invalid: there is no unique or primary key constraint on table '"ROOT"."PROJECT_SITE"' that matches the number and types of the columns in the foreign key."

And indeed there is none, because Hibernate hasn't added it yet:

create table project (id integer not null, primary key (id))
create table project_site (id integer not null, project_id integer, site_id integer, primary key (id))
create table site (id integer not null, primary key (id))
create table user_roles (id integer not null, role varchar(255), user_name varchar(255), project_id integer, site_id integer not null, primary key (id))
alter table project_site add constraint FK380054AD6E971B37 foreign key (project_id) references project
alter table project_site add constraint FK380054AD6E3AE95D foreign key (site_id) references site
alter table user_roles add constraint FK73429949AACF5CB1 foreign key (project_id, site_id) references project_site (project_id, site_id)

16:27:35,834 ERROR SchemaExport: schema export unsuccessful

org.hibernate.JDBCException: Error during DDL export
	at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:345)
	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262)
	at org.wfp.rita.datafacade.HibernateJoinPropertyRefMultipleColumnsFails.test(HibernateJoinPropertyRefMultipleColumnsFails.java:149)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at junit.framework.TestCase.runBare(TestCase.java:127)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at junit.framework.TestSuite.runTest(TestSuite.java:208)
	at junit.framework.TestSuite.run(TestSuite.java:203)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Constraint 'FK73429949AACF5CB1' is invalid: there is no unique or primary key constraint on table '"ROOT"."PROJECT_SITE"' that matches the number and types of the columns in the foreign key.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira