UNSIGNED constraint for INT(11) in Hibernate

Question

I have been trying to add constraint UNSIGNED to a column using MYSQL. Below is my table.

public class UserActivity implements BaseEntity{

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
Long id;

Long entityId;

@Column(columnDefinition = "enum('QUESTION','ANSWER', 'COMMENT', 'TAGS')")
@Enumerated(EnumType.STRING)
Type type;

@ManyToOne
@JoinColumn(nullable = false, columnDefinition="INT(11) UNSIGNED")
CustomerEntity user;

But it is not creating that constraint for user column in the database due to which foreign key and index are not setting up. This and this could not help. Kindly help me out. Thanks

LOGS:

Hibernate:
alter table UserActivity
    add constraint FK_jfw954ii6gw7mbqdth6y3n0rs
    foreign key (user_Id)
    references customerDb.customer (Id)
12:31:30.350 [localhost-startStop-1] ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: alter table UserActivity add constraint FK_jfw954ii6gw7mbqdth6y3n0rs foreign key (user_Id) references customerDb.customer (Id)
12:31:30.350 [localhost-startStop-1] ERROR o.h.tool.hbm2ddl.SchemaExport - Can't create table 'community_db.#sql-43f0_5a07' (errno: 150)
Hibernate:

MySQL Queries :

ALTER TABLE `community_db`.`Question` 
ADD INDEX `fk_Question_Customer_idx` (`author_Id` ASC);
ALTER TABLE `community_db`.`Question` 
ADD CONSTRAINT `fk_Answer_Customer`
FOREIGN KEY (`author_Id`)
REFERENCES `customerDb`.`customer` (`Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

and below are the logs for the above mysql queries :

Error Code: 1005. Can't create table 'community_db.#sql-43f0_5d19' (errno: 150) 0.048 sec

In the logs I searched for 'UNSIGNED' but could not find. There are no queries for UNSIGNED and show_sql and format_sql is true — Pallav Jha, May 12 '16 at 06:52
Could you also update with the exact MySQL `ALTER TABLE` statement which you would run on your own to add the constraint? This might be useful to try to debug this. — Tim Biegeleisen, May 12 '16 at 07:22
Yup, where ever there is a many to one relation with customer this happens. Question also contains @manyToOne Customer — Pallav Jha, May 12 '16 at 07:35
I think your Hibernate design is off, rather than the column being the issue. — Tim Biegeleisen, May 12 '16 at 07:42
I also feel that because there wasn't any query for UNSIGNED contraint. — Pallav Jha, May 12 '16 at 07:47

score 4 · Accepted Answer · edited Jan 23 '17 at 08:10

The columnDefinition property value of the @JoinColumn or the foreign key must be equal to the columnDefinition property value of the @Column annotation present in the Entity Id, i.e., they must have the same property.

Below is the CustomerEntity

@PersistenceUnit(unitName="persistenceUnit")
@Entity
@Table(name = "customer", schema = "customerDb")
public class CustomerEntity {
    @Id
    @Column(name = "Id", nullable = false, columnDefinition = "INT(11) UNSIGNED")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
..................
}

And in the UserActivity which would contain the customer Id as the foreign key the same same columndDefinition must be present.

public class UserActivity implements BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;

    Long entityId;

    @ManyToOne
    @JoinColumn(nullable = false, columnDefinition = "INT(11) UNSIGNED")
    CustomerEntity user;
..................
}

I created that table using IntelliJIdea which did not added those property due to which I got the error.

UNSIGNED constraint for INT(11) in Hibernate

1 Answers1