"Unique index or primary key violation" for unknown constraint
Peter
I am using h2 version 1.2.137 with hibernate 3.5.1-Final and I am
getting a strange Unique index exception:
Unique index or primary key violation: "MYTABLE_DATA ON
PUBLIC.MYTABLE(ID, CREATEDAT, ..., ...)"
I can open the db, but every time I want to store sth. this exception
occurs. Is my database corrupt like suggested here: http://fri13th.com/blog/archives/285
? What does MYTABLE_DATA ON ... mean? And why do I get a different
exception if I explicitely violate my unique key constraint for "name"
in that table?
Unique index or primary key violation: "CONSTRAINT_INDEX_5 ON
PUBLIC.MYTABLE(NAME)"
Regards,
Peter.
Thomas Mueller
> Unique index or primary key violation: "MYTABLE_DATA ON
> PUBLIC.MYTABLE(ID, CREATEDAT, ..., ...)"
The problem is that there was a primary key violation. I agree it's a
weird error message, I will fix that in the next release. However,
it's definitely a primary key violation. Here is a test case that
throws a similar exception:
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello');
INSERT INTO TEST VALUES(1, 'World');
-- Unique index or primary key violation: "TEST_DATA ON PUBLIC.TEST(ID, NAME)"
> Is my database corrupt like suggested here: http://fri13th.com/blog/archives/285
No. This blog article is quite old (2007). There were a lot of changes
in H2 since then. And this error message is not related to corruption.
Regards,
Thomas
Peter
thanks for the quick response, this helped me.
But why a different message occur for other ('normal') cases?
Is it different because the primary key (MYTABLE.ID) instead of the
additional unique constraint (on MYTABLE.NAME) is violated?
Regards,
Peter.
On Jul 13, 8:10 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Peter
now. this seems to be really a problem of hibernate+h2. hibernate
generates already existing ids!!??
The observations I made when importing data from an external data
source:
1. I executed the import once. A lot of data (say X*1000) -> every
thing is fine
2. The next import -> exception
But if I would have only imported less data (say X*100) in step 1. I
can execute this step multiple times without an error.
What id generation strategy should be used when using h2 with
hibernate?
Regards,
Peter.
Peter
Peter
It seems to me that a mapping of:
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
and changing the config to the new generators in hibernate.cfg.xml:
<property name="hibernate.id.new_generator_mappings">true</property>
// http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html#mapping-declaration-id-enhanced
solve my problem!
Before I had the (incorrect?) mapping of:
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
private Long id;
I could conclude that it was either a unique key constraint violation
(of primary key) of hibernate or
my fault with the wrong explicit generationtype.table and not .auto!
Regards,
Peter.
Thomas Mueller
> @GeneratedValue(strategy = GenerationType.TABLE)
This seems to be a relatively common problem (at least I found others
get similar problems by doing a Google search).
> But why a different message occur for other ('normal') cases?
> Is it different because the primary key (MYTABLE.ID) instead of the
> additional unique constraint (on MYTABLE.NAME) is violated?
The problem is (warning: technical details ahead), if you have a
single column primary key, and the data type is INT, BIGINT, or
similar, then there is no actual index created. Instead, the "main"
lookup method of the table is used (which is also a b-tree structure,
like regular indexes, but it uses different class and storage format:
class PageDataIndex instead of PageBtreeIndex). That's where the
"_DATA" comes from in the error message. There is always a
PageDataIndex even if there is no primary key, so the database is
re-using that if possible. but unfortunately it's also re-using the
name of that object in the error message, which is wrong. This is what
I will fix. Other databases call this a "clustered index" by the way:
http://en.wikipedia.org/wiki/Index_%28database%29#Clustered
Regards,
Thomas