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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
