[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-4543) Hibernate does not allow a many-to-one to property-ref a composite unique key (properties element) that is based on many-to-one associations

Hibernate does not allow a many-to-one to property-ref a composite unique key (properties element) that is based on many-to-one associations -------------------------------------------------------------------------------------------------------------------------------------------- Key: HHH-4543 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4543 Project: Hibernate Core Issue Type: Improvement Components: core Affects Versions: 3.3.2 Environment: Ubuntu 8.04, mysql 5.0.51a-3ubuntu5.4, Connector/J, InnoDB tables Reporter: Chris Wilson Attachments: hibernate-testcase-unique-key-on-association.zip Original Estimate: 2 hours Remaining Estimate: 2 hours The attached test case demonstrates the problem. Our situation is: We have four tables in this minimal test case: projects, sites, project_sites and site_cost. project_sites is linked to both projects and sites by a unique key spanning both, defined as a <properties> element in the Hibernate mapping. The <property> elements inside this <properties> name many-to-one associations, not stand-alone <property> elements. See below for justification. site_cost references this unique key using a project and a site as well, so it does not reference the primary key of project_sites, as that would duplicate data (site_id) and allow conflicts in our real-world code, where the site_id column is used in many other associations as well. Please bear with us on this database structure, as it's used for sharding and replicating tables between offline nodes based on site_ids. We generate our schema from the Hibernate mappings to allow database portability, using Hibernate's SchemaExport. The generation fails when adding the constraint to site_cost, because it incorrectly tries to use the database field names "site" and "project" (which don't exist, but are the names of the associations) in building the constraint, instead of "site_id" and "project_id" which do exist. The failing statement is: alter table site_cost add index FKE02EFDC52F2411E6 (project_id, site_id), add constraint FKE02EFDC52F2411E6 foreign key (project_id, site_id) references project_site (project, site) Which results in the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...] at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:383) at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:341) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262) at org.aptivate.hibernate.test.ReferencePropertiesTest.testReferenceToProperties(ReferencePropertiesTest.java:63) [...] One way to work around this is to change the <properties> in ProjectSite.hbm.xml to reference individual properties instead of many-to-one relationships, and add those properties to the mapping and the POJO class. However, I don't wish to add a useless and dangerous API (to directly set to my POJOs. It should be possible to use the many-to-one mapping by extracting the column name(s) from it, and adding them to the unique index, but I guess Hibernate does not support that at present. The documentation is not clear on whether it should support it or not. Perhaps it's implied that a properties > property.name should name an actual property and not a many-to-one association, but it appears sparsely documented to me, and this would definitely be a useful feature for us. I'm sorry for not (yet) providing a patch myself, but I'm pretty new to Hibernate and not familiar with the code, and I currently can't build it due to the number of dependencies (and not being good with Maven).