AS 4.0.5.GA
Seam 1.1.5.GA
Oracle 9i

While trying to build and run the Seam Blog example, I stumbled across this hibernate-generated ddl, which is illegal in Oracle:

create table HitCount (
blog_name varchar2(255 char) not null,
pageviews number(10,0) not null,
primary key (blog_name), <------- Oracle objects to
unique (blog_name) <------- this
)

For a while, I thought it was a bug in hibernate-tools, but after referring to my copy of GK’s epic Java Persistence with Hibernate, I now think it is due to improper use of JPA annotations…

There are 3 java classes that define entities: Blog, BlogEntry and HitCount.

The author intended that there should be a one-to-one relationship between Blog and HitCount. So in Blog.java, you see:

@Entity
public class Blog {

/** the name of the blog */
@Id @Length(max=70)
private String name;

…

@OneToOne(optional=false, mappedBy="blog")
private HitCount hitCount;

The @OneToOne clause says that the relationship with HitCount must be bi-directional. So hbm2ddl will generate a foreign key column in the HitCount table. That column’s name will be a concatenation of the mappedBy attribute value (“blog”) plus an underscore plus the Blog table’s primary key column name (“name”); i.e. a foreign key column named “blog_name ” gets generated.

Also, since it is a one-to-one relationship, hbm2ddl also generates a unique constraint for that foreign key column as well.

Now look at HitCount.java, and see that the primary key column is also explicitely named “blog_name”. This is why the CREATE statement (above) for HitCount ends up with both unqiue and primary key of the same name.

@Entity
public class HitCount
{
@Id
@Column(name="blog_name")
private String blogName;

…
}
First point: should hbm2ddl be aware of this conflict ? Perhaps it doesn’t matter to other DBs such as hqsl.

Second point: Gavin expliciely advises us to not use ‘natural’ keys as primary keys. Section 4.2.3 of his book explains this (hope I don’t get in trouble for showing this extract!):

…Experience has shown that natural keys almost always cause problems in the
long run. A good primary key must be unique, constant, and required (never null
or unknown). Few entity attributes satisfy these requirements, and some that do
can’t be efficiently indexed by SQL databases (although this is an implementation
detail and shouldn’t be the primary motivation for or against a particular key). In
addition, you should make certain that a candidate key definition can never
change throughout the lifetime of the database before making it a primary key.
Changing the value (or even definition) of a primary key, and all foreign keys that
refer to it, is a frustrating task. Furthermore, natural candidate keys can often be
found only by combining several columns in a composite natural key. These composite
keys, although certainly appropriate for some relations (like a link table in
a many-to-many relationship), usually make maintenance, ad-hoc queries, and
schema evolution much more difficult.
For these reasons, we strongly recommend that you consider synthetic identifiers,
also called surrogate keys. Surrogate keys have no business meaning—they’re
unique values generated by the database or application…

If Gavins advice had been taken, this forum posting wouldn’t have been necessary.
Hope my analysis is correct and understandable.

Ian
Sydney