Incompatible data types while ALTER table in HSQL with Hibernate

Question

How fix next error?

ERROR SchemaExport:484 - HHH000389: Unsuccessful: alter table CATEGORY_RELATIONS add constraint FK2bn4xlg661b5xbx2qnwi1aqv0 foreign key (CATEGORY_RELATIONS_PARENT_ID) references CATEGORY ERROR SchemaExport:485 - incompatible data types in combination in statement [alter table CATEGORY_RELATIONS add constraint FK2bn4xlg661b5xbx2qnwi1aqv0 foreign key (CATEGORY_RELATIONS_PARENT_ID) references CATEGORY]

hibernate.version 5.0.7.Final

hsqldb.version 2.3.3

Property, used for session factory

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Category

@Entity
@Table(name = "CATEGORY",
        indexes = {
                @Index(name = "CATEGORY_NAME_INDEX",
                        columnList = "CATEGORY_NAME")})
public class Category extends JsonNamedModel {
    @Id
    @Column(name = "CATEGORY_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    @Column(name = "CATEGORY_NAME")
    private String name;
    @Column(name = "CATEGORY_IMAGE")
    private String image;
    @OneToOne(cascade = CascadeType.REMOVE, fetch = FetchType.LAZY)
    @JoinTable(name = "CATEGORY_RELATIONS",
            joinColumns = {
                    @JoinColumn(name = "CATEGORY_RELATIONS_CATEGORY_ID", referencedColumnName = "CATEGORY_ID")},
            inverseJoinColumns = {
                    @JoinColumn(name = "CATEGORY_RELATIONS_PARENT_ID", referencedColumnName = "CATEGORY_ID")})
    private Category parent;
    @OneToMany(cascade = CascadeType.REMOVE, fetch = FetchType.EAGER, mappedBy = "parent")
    private List<Category> children;//...
}

CategoryRelations

@Entity
@Table(name = "CATEGORY_RELATIONS")
@IdClass(CategoryRelations.CategoryRelationsPrimaryKey.class)
public class CategoryRelations implements Serializable {
    @Id
    @Column(name = "CATEGORY_RELATIONS_CATEGORY_ID")
    private String categoryId;
    @Id
    @Column(name = "CATEGORY_RELATIONS_PARENT_ID")
    private String parentId;

    @Entity
    @IdClass(CategoryRelationsPrimaryKey.class)
    public static class CategoryRelationsPrimaryKey implements Serializable {
        private long categoryId;
        private long parentId;
    }//...
}

Todd Todd · Accepted Answer · 2016-04-08T14:25:09

I think it's complaining because your types don't match up. In CATEGORY_RELATIONS you have the key types as String but in CATEGORY you have the primary key as an int. While in actual practice you might only store integer data in both fields, the DB engine can't prove that. There's nothing stopping somebody from putting a non-integer in CATEGORY_RELATIONS.categoryId and making it so the FK could never be satisfied.

Try changing CATEGORY_RELATIONS.categoryId to an int.

And now that I look at it, your PK class shows them as longs. Try switching all of the types in your CategoryRelations object (and possibly the CATEGORY_RELATIONS table) to all be the same types.

Incompatible data types while ALTER table in HSQL with Hibernate

1 Answers