Databases
StackOverflow2013
Postsdbo
POShould Hibernate be able to handle overlapping foreign keys?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POShould Hibernate be able to handle overlapping foreign keys?
    primarykey
    data
    text
    <p>I have a table that has two foreign keys to two different tables with both foreign keys <strong>sharing one column</strong>:</p> <pre><code>CREATE TABLE ZipAreas ( country_code CHAR(2) NOT NULL, zip_code VARCHAR(10) NOT NULL, state_code VARCHAR(5) NOT NULL, city_name VARCHAR(100) NOT NULL, PRIMARY KEY (country_code, zip_code, state_code, city_name), FOREIGN KEY (country_code, zip_code) REFERENCES Zips (country_code, code), FOREIGN KEY (country_code, state_code, city_name) REFERENCES Cities (country_code, state_code, name) ) </code></pre> <p>As you can see, there are two FKs sharing country_code (coincidentally referencing the same column at the end of the referentiation path). The entity class looks like (JPA 1.0 @IdClass):</p> <pre><code>@Entity @Table(name = "ZipAreas") @IdClass(value = ZipAreaId.class) public class ZipArea implements Serializable { @Id @Column(name = "country_code", insertable = false, updatable = false) private String countryCode; @Id @Column(name = "zip_code", insertable = false, updatable = false) private String zipCode; @Id @Column(name = "state_code", insertable = false, updatable = false) private String stateCode; @Id @Column(name = "city_name", insertable = false, updatable = false) private String cityName; @ManyToOne @JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "zip_code", referencedColumnName = "code")}) private Zip zip = null; @ManyToOne @JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code", insertable = false, updatable = false), @JoinColumn(name = "state_code", referencedColumnName = "state_code"), @JoinColumn(name = "city_name", referencedColumnName = "name")}) private City city = null; ... } </code></pre> <p>As you can see I flagged the countryCode property and city's country_code @JoinColumn as read-only (insertable = false, updatable = false). Hibernate fails with this saying:</p> <pre><code>Exception in thread "main" javax.persistence.PersistenceException: [PersistenceUnit: geoinfo] Unable to configure EntityManagerFactory at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:374) at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:56) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:48) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:32) at tld.geoinfo.Main.main(Main.java:27) Caused by: org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed: tld.geoinfo.model.ZipAreacity at org.hibernate.cfg.Ejb3Column.checkPropertyConsistency(Ejb3Column.java:563) at org.hibernate.cfg.AnnotationBinder.bindManyToOne(AnnotationBinder.java:2703) at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1600) at org.hibernate.cfg.AnnotationBinder.processIdPropertiesIfNotAlready(AnnotationBinder.java:796) at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:707) at org.hibernate.cfg.Configuration$MetadataSourceQueue.processAnnotatedClassesQueue(Configuration.java:3977) at org.hibernate.cfg.Configuration$MetadataSourceQueue.processMetadata(Configuration.java:3931) at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1368) at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1345) at org.hibernate.ejb.Ejb3Configuration.buildMappings(Ejb3Configuration.java:1477) at org.hibernate.ejb.EventListenerConfigurator.configure(EventListenerConfigurator.java:193) at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:1096) at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:278) at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:362) ... 4 more </code></pre> <p>This looks pretty basic to me honestly. "Mixing insertable and non insertable columns in a property is not allowed" is such a weak "excuse", isn't it?</p> <p>Should Hibernate be able to handle this, e.g. according to the JPA spec? Is this a bug?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKawu
    1. USKawu
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. USKawu
      1. VTFavorite
    2. VO
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. USbanterCZ
      1. VTFavorite
    1. COHere's a JavaSE, HSQLDB, Ant standalone app: http://www.kawoolutions.com/media/geoinfo-hib-overlapping-fks.zip
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. USKawu
    2. COhibernate does dislike composite primary keys - in some situations like this one it messes up. try to refactor your database to use single column primary keys.
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. USAndreas Petersson
    3. COYeah Hibernate sucks at composite keys. In any case, this has never been reported, now it is: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6221
      singulars
      1. POShould Hibernate be able to handle overlapping foreign keys?
      1. USKawu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload