title: Hibernate cant find the correct foreign key
body: <p>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.</p>
  
  <p>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</p>
  
  <ul>
  <li>Hibernate creates tables automatically </li>
  <li>Hibernate creates foreign key relationships</li>
  <li>Hibernate runs my drop table scripts (that succeeded since there is no data so no foreign key rule has been broken)</li>
  <li>Hibernate runs my create table scripts</li>
  <li>Hibernate runs my add foreign constraint scripts</li>
  <li>Hibernate runs my insert data scripts</li>
  <li>Test is executed</li>
  <li>Hibernate tries to remove the foreign key and it fails. </li>
  </ul>
  
  <p>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.</p>
  
  <p>Any idea how to force hibernate to find out the actual foreign key? Any way to get around this problem? </p>
  
  <p>Thanks everyone</p>
  
  <p>Class for which hibernate creates the table</p>
  
  <p>TodoGroup.java</p>
  
  <pre><code>@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&lt;ToDoItem&gt; items;
  </code></pre>
  
  <p>hibernate adding the constraint</p>
  
  <pre><code>alter table ToDoGroups 
          add constraint FK790BA1FAFE315596 
          foreign key (UserSettingsId) 
          references UserSettings
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>drop fk it tries to execute</p>
  
  <pre><code>alter table ToDoGroups drop constraint FK790BA1FAFE315596
  java.sql.SQLException: Constraint not found FK790BA1FAFE315596 in table: TODOGROUPS in statement [alter table ToDoGroups drop constraint FK790BA1FAFE315596]
  </code></pre>
  
  <p>tries to remove the table which fails due to the constrain that I have set in my create.sql script</p>
  
  <pre><code>    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]
  </code></pre>
  
  <p>Update</p>
  
  <p>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. </p>
  
  <p>So how does hibernate know what foreign key to use? It uses the same key that</p>
  
  <p>first statement it executes</p>
  
  <pre><code>alter table ToDoGroups drop constraint FK790BA1FAFE315596
  </code></pre>
  
  <p>then it drops all of the tables</p>
  
  <pre><code>drop table ToDoGroups if exists
  </code></pre>
  
  <p>then it creates table </p>
  
  <pre><code>create table ToDoGroups (
      id bigint generated by default as identity (start with 1),
      Name varchar(50),
      UserSettingsId bigint,
      primary key (id)
  )
  </code></pre>
  
  <p>then it adds the same FK</p>
  
  <pre><code>alter table ToDoGroups 
      add constraint FK790BA1FAFE315596 
      foreign key (UserSettingsId) 
      references UserSettings
  </code></pre>
  
  <p>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?</p>