CODEWITHSUNDEEP
javahibernatejunithsqldb
Boris Horvat
Boris Horvat

Reputation: 573

Hibernate cant find the correct foreign key

I am having a problem that hibernate tries to drop foreign keys that dont exist instead of the one that exists. My scenario looks like this.

I want to run a junit tests, before ever test I want to create DB and after ever test I want to drop it. For that I use hibernate create-drop property. However the tricky part is that I want to create my own tables as a way to test newly added sql and verify that it will run fine once I deploy it to the production db server. So what happens is this

The reason hibernate has not be able to remove it is cause it tried to remove that one that hibernate created and not the one that was created by my scripts.

Any idea how to force hibernate to find out the actual foreign key? Any way to get around this problem?

Thanks everyone

Class for which hibernate creates the table

TodoGroup.java

@Entity
@Table(name = "ToDoGroups")
public class ToDoGroup implements Serializable{


    @Id
    @GeneratedValue
    private Long id;
    @Column(name = "Name", length = 50)
    private String name;
    @ManyToOne
    @JoinColumn(name = "UserSettingsId")
    @XmlTransient
    private UserSettings userSettings;
    @OneToMany(mappedBy = "group", cascade = CascadeType.ALL)
    private List<ToDoItem> items;

hibernate adding the constraint

alter table ToDoGroups 
        add constraint FK790BA1FAFE315596 
        foreign key (UserSettingsId) 
        references UserSettings

running my own tables that work fine since there is no data so I can remove what hibernate created in order to verify my sql

DROP TABLE IF EXISTS ToDoGroups;

CREATE TABLE ToDoGroups (ID BIGINT NOT NULL IDENTITY, Name VARCHAR(50) NOT NULL, UserSettingsId BIGINT NOT NULL, PRIMARY KEY (ID));

ALTER TABLE ToDoGroups ADD FOREIGN KEY (UserSettingsID) REFERENCES UserSettings (ID);

drop fk it tries to execute

alter table ToDoGroups drop constraint FK790BA1FAFE315596
java.sql.SQLException: Constraint not found FK790BA1FAFE315596 in table: TODOGROUPS in statement [alter table ToDoGroups drop constraint FK790BA1FAFE315596]

tries to remove the table which fails due to the constrain that I have set in my create.sql script

    drop table ToDoGroups if exists
java.sql.SQLException: Table is referenced by a constraint in table SYS_REF_SYS_FK_808_810 table: TODOITEMS in statement [drop table ToDoGroups if exists]

Update

I have also noticed that hibernate when it first starts before it creates the tables (so this is way before my scripts are run), tries to remove foreign key in order to drop any table that exists.

So how does hibernate know what foreign key to use? It uses the same key that

first statement it executes

alter table ToDoGroups drop constraint FK790BA1FAFE315596

then it drops all of the tables

drop table ToDoGroups if exists

then it creates table 

create table ToDoGroups (
    id bigint generated by default as identity (start with 1),
    Name varchar(50),
    UserSettingsId bigint,
    primary key (id)
)

then it adds the same FK

alter table ToDoGroups 
    add constraint FK790BA1FAFE315596 
    foreign key (UserSettingsId) 
    references UserSettings

I think my question here is how does hibernate know what FK to use. It used the same FK in the first drop statement when there was even no table. Later it used that some FK to create the relationship. Shouldn't hibernate first check if the table exists and then tries to determine what is the FK?

Upvotes: 2

Views: 3293

Answers (1)

ben75
ben75

Reputation: 28726

As far as I understand, your problem is that your own script and hibernate don't use the same constraint name.

You can specify a constraint name used by hibernate with this annotation on your relationship:

@ForeignKey(name = "fk_UserSettings")

And additionally, in your create.sql:

ALTER TABLE ToDoGroups ADD CONSTRAINT fk_UserSettings FOREIGN KEY (UserSettingsID) REFERENCES UserSettings (ID);

I think my question here is how does hibernate know what FK to use. It used the same FK in the first drop statement when there was even no table. Later it used that some FK to create the relationship. Shouldn't hibernate first check if the table exists and then tries to determine what is the FK?

The foreign key name used by hibernate is the concatenation of 

"FK_" + hashcode of referenced entity name + hash code of referenced columns name on that entity.

So it is not a randomly generated key (you will see that it will change if you change your entity name). And that's how hibernate knows the name of the fk to drop (hibernate is expecting that the constraint was created by hibernate with this well known naming strategy).

Hibernate use the name of the constraint to manipulate it. It don't compare the "rule" coded in constraints associated with a table to see if the constraint is already there or not.

Upvotes: 3

Related Questions