Hi,
I'm having a problem with the DDL generated for Derby (10.4.2.0) when using Hibernate (3.2.6.ga) with JPA annotations (1.0). Derby doesn't seem to like the way Hibernate generates a DDL for the @JoinTable annotation with @UniqueConstraint on the mapping columns.
Here's the example code for the specific parent-child relationship I'm using.
Parent.java:
Code:
package com.mycompany.DerbyHibernate;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.UniqueConstraint;
@Entity
public class Parent {
@Id
@GeneratedValue
private Long id;
@Column(nullable=false,unique=true)
private String name;
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="Parent_Child",
joinColumns={@JoinColumn(name="Parent",nullable=false)}
,inverseJoinColumns={@JoinColumn(name="Child",nullable=false)}
,uniqueConstraints={@UniqueConstraint(columnNames={"Parent","Child"})}
)
private Set<Child> children;
public Parent(){
super();
setChildren(new HashSet<Child>());
}
/**
* @return the id
*/
public Long getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(Long id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the children
*/
public Set<Child> getChildren() {
return children;
}
/**
* @param children the children to set
*/
public void setChildren(Set<Child> children) {
this.children = children;
}
}
Child.java:
Code:
package com.mycompany.DerbyHibernate;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Entity
public class Child {
@Id
@GeneratedValue
private Long id;
@Column(nullable=false,unique=true)
private String name;
@Column(nullable=false)
private String description;
public Child(){
super();
}
/**
* @return the id
*/
public Long getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(Long id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the description
*/
public String getDescription() {
return description;
}
/**
* @param description the description to set
*/
public void setDescription(String description) {
this.description = description;
}
}
Here's the log snippet:
Quote:
15:11:08,691 [main] DEBUG SchemaUpdate:149 - create table Parent_Child (Parent bigint not null, Child bigint not null, primary key (Parent, Child), unique (Parent, Child))
15:11:08,695 [main] ERROR SchemaUpdate:155 - Unsuccessful: create table Parent_Child (Parent bigint not null, Child bigint not null, primary key (Parent, Child), unique (Parent, Child))
15:11:08,695 [main] ERROR SchemaUpdate:156 - Constraints 'SQL090709151108691' and 'SQL090709151108690' have the same set of columns, which is not allowed.
15:11:08,695 [main] DEBUG SchemaUpdate:149 - alter table Parent_Child add constraint FK5AF11D4743005EDE foreign key (Child) references Child
15:11:08,700 [main] ERROR SchemaUpdate:155 - Unsuccessful: alter table Parent_Child add constraint FK5AF11D4743005EDE foreign key (Child) references Child
15:11:08,700 [main] ERROR SchemaUpdate:156 - 'ALTER TABLE' cannot be performed on 'PARENT_CHILD' because it does not exist.
15:11:08,701 [main] DEBUG SchemaUpdate:149 - alter table Parent_Child add constraint FK5AF11D4748AC316E foreign key (Parent) references Parent
15:11:08,702 [main] ERROR SchemaUpdate:155 - Unsuccessful: alter table Parent_Child add constraint FK5AF11D4748AC316E foreign key (Parent) references Parent
15:11:08,702 [main] ERROR SchemaUpdate:156 - 'ALTER TABLE' cannot be performed on 'PARENT_CHILD' because it does not exist.
So, it looks like Derby doesn't like the fact that the DDL is producing a primary key constraint along with a unique constraint for the same two columns. This works fine for MySQL, as shown in the following log snippet when running with MySQL 5 (InnoDB engine):
Quote:
15:15:38,293 [main] DEBUG SchemaUpdate:149 - create table Child (id bigint not null auto_increment, description varchar(255) not null, name varchar(255) not null unique, primary key (id)) ENGINE=InnoDB
15:15:38,339 [main] DEBUG SchemaUpdate:149 - create table Parent (id bigint not null auto_increment, name varchar(255) not null unique, primary key (id)) ENGINE=InnoDB
15:15:38,384 [main] DEBUG SchemaUpdate:149 - create table Parent_Child (Parent bigint not null, Child bigint not null, primary key (Parent, Child), unique (Parent, Child)) ENGINE=InnoDB
15:15:38,403 [main] DEBUG SchemaUpdate:149 - alter table Parent_Child add index FK5AF11D4743005EDE (Child), add constraint FK5AF11D4743005EDE foreign key (Child) references Child (id)
15:15:38,484 [main] DEBUG SchemaUpdate:149 - alter table Parent_Child add index FK5AF11D4748AC316E (Parent), add constraint FK5AF11D4748AC316E foreign key (Parent) references Parent (id)
15:15:38,493 [main] INFO SchemaUpdate:160 - schema update complete
As you can see, both the MySQL DDL and the Derby DDL statements try to add both a Primary Key and a Unique contraint, but Derby does not allow this because it is redundant, as primary keys are inherently unique (see
http://markmail.org/message/amuqjshfo7irlckv for a discussion on this issue on the Derby mailing list).
If anyone has any insight into how to make this work correctly, it would be appreciated. I have a test project ready in case I need to file a JIRA for it, but I thought I'd post to the forums first.
Login
FAQ
Search