Caveat example: Problems with Ingres Dialect

carlolf · **Posted:** Tue Jun 12, 2007 4:23 am

Hi,

my next project (and in effect: my first with Hibernate) project will be based on a Ingres Database. So I'm just familiarizing myself with Ingres and Hibernate.

I first tried the Caveat example in the JPA version with Ingres.

First thing I noticed: you cannot use Identity key generators with Ingres; but the example builds on those: BAD. Anyway, i changed the strategy to sequence.

Second problem: hbm2DDL produces this Create statement:

Code:

create table CATEGORY_ITEMS_BY_USER (CATEGORY_ID bigint not null, USER_ID bigint not null, ITEM_ID bigint with null, primary key (CATEGORY_ID, ITEM_ID))

so the JDBC-driver (or the database?) complains:

Code:

CREATE TABLE: A column in a UNIQUE constraint has been 
defined as WITH NULL (on table 'category_items_by_user')

.

With this problem, I have no idea how to solve it. I hope there won't be more of this kind of problems with Ingres.

Can someone help?

Carlo.

christian · **Posted:** Tue Jun 12, 2007 4:49 am

That column should not be NULL and Ingres seems to be one of the few databases that (correctly) complains about it. I guess you can put an optional='false' or something on the mapping in Category.java in this block:

Code:

@ManyToMany
    @org.hibernate.annotations.MapKeyManyToMany(
        joinColumns = @JoinColumn(name = "ITEM_ID")
    )
...

Note that good usecases for MapKeyManyToMany are _extremely_ rare, this is for demonstration purposes only.