Unique constraints and indexes with _INDEX_<NUM>

[sam3]

I've noticed that when I create a multi-column unique constraint, H2
also creates an index of the same name but with _INDEX_<NUM> on the
end, where <NUM> is an integer that probably comes from an internal
sequence. That's not unreasonable, but if I drop the unique constraint
it doesn't drop the index. If I then recreate the unique constraint
with a different name, it keeps the old index with the old name. I've
found that I can overcome this by manually updating the index name as
follows (I'm changing all my names to a new scheme by the way):

ALTER INDEX "UQ_ORDERITEM:ORDER_ID+LINE_NUMBER_INDEX_6" RENAME TO
"ORDERITEM__ORDER_ID__LINE_NUMBER_UQ";

But, this only works if I know ahead of time that 6 is the magic
number. I need to put this code in a Liquibase migration that will run
against lots of developers machines with their databases in all sorts
of funny states and I don't want it to barf. A colleague needed 8 as
the magic number for instance. I can't think of a way to do this
though, since ALTER INDEX won't allow me to use a variable or sub-
select for the name that needs changing (I tried selecting the old
name with a LIKE query).

Any ideas very welcomed!

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

> if I drop the unique constraint it doesn't drop the index.

I can't reproduce this problem using the current version of H2. See my
test case below. What version of H2 do you use? Could you provide a
reproducible test case?

drop table test;
create table test(a int, b int);
alter table test add constraint x unique(a, b);
select distinct index_name
from INFORMATION_SCHEMA.INDEXES;
-- X_INDEX_2
alter table test drop constraint x;
select distinct index_name
from INFORMATION_SCHEMA.INDEXES;
-- no rows

Regards,
Thomas

[sam carr]

Hi Thomas,

I'm using version 1.2.132 at the moment. If I run your example SQL it
does seem to behave correctly, so perhaps there's something more
complicated about the scenario I was dealing with. That scenario is
now but a memory as we've worked around the difficulty and moved on,
so I'll leave it there. If I had more time I'd try to figure it out in
case I can help with an odd bug.

Thanks for your help

Sam