Ok thanks for the response, I logged an issue - https://github.com/h2database/h2database/issues/294
On Sunday, May 29, 2016 at 10:02:55 AM UTC-5, Noel Grandin wrote: > > there are two separate bugs here. > To work around them, declare the PK as AUTO_INCREMENT and create both > constraints using separate commands. > > Also please log this in our bug tracker at > https://github.com/h2database/h2database > > On 27 May 2016 at 22:41, Ryan Rupp <[email protected] <javascript:>> > wrote: > >> I'm trying to figure out if there's a way to drop a unique constraint >> using a SQL statement that will work for both MySQL and H2 (setup in MySQL >> compatibility mode). Basically MySQL is expecting the syntax: >> >> ALTER TABLE <table> DROP INDEX <index_name> >> >> However, when this is used with H2 it can't find the index because it >> appears that H2 is creating the index suffixed with the keyword "INDEX_C" >> or some other test scenarios I did it looks like during renames the index >> gets suffixed with "INDEX_<number>". Here's some example code that >> reproduces the issue: >> >> import java.io.InputStream; >>> >>> import java.io.InputStreamReader; >>> >>> import java.sql.Connection; >>> >>> import java.sql.DriverManager; >>> >>> import java.sql.ResultSet; >>> >>> import java.sql.SQLException; >>> >>> >>> import org.h2.tools.RunScript; >>> >>> import org.junit.Test; >>> >>> >>> public class TestH2 { >>> >>> >>> @Test >>> >>> public void testIndexIssue() throws SQLException, >>> ClassNotFoundException { >>> >>> Class.forName("org.h2.Driver"); >>> >>> InputStream in = getClass().getResourceAsStream("script.sql"); >>> >>> if (in == null) { >>> >>> System.out.println("Please add the file script.sql to the >>> classpath, package " >>> >>> + getClass().getPackage().getName()); >>> >>> } else { >>> >>> Connection conn = DriverManager.getConnection( >>> "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;MODE=MYSQL;IGNORECASE=TRUE" >>> ); >>> >>> RunScript.execute(conn, new InputStreamReader(in)); >>> >>> ResultSet rs = conn.getMetaData().getIndexInfo(null, null, >>> "test_table", false, true); >>> >>> while (rs.next()) { >>> >>> System.out.println(rs.getString("INDEX_NAME")); >>> >>> } >>> >>> conn.close(); >>> >>> } >>> >>> } >>> >>> } >>> >> >> Then the script.sql it loads has the following statements: >> >> CREATE TABLE test_table ( >>> >>> id serial, >>> >>> unique_field bigint unsigned not null, >>> >>> CONSTRAINT pk_test_table PRIMARY KEY ( >>> >>> id ASC >>> >>> ), >>> >>> CONSTRAINT uk_test UNIQUE KEY ( >>> >>> unique_field >>> >>> ) >>> >>> ); >>> >>> >>> /* Comment this out to see the available indexes in the test output */ >>> >>> ALTER TABLE test_table DROP INDEX uk_test; >>> >>> >>> >> >> Which will then fail with: >> >> org.h2.jdbc.JdbcSQLException: Index "UK_TEST" not found; SQL statement: >> >> If you comment out the drop index line at the end the test will print out >> the indexes which shows: >> >> primary_key_c >> >> uk_test_index_c >> >> >> I'm using Flyway for migrations with H2 as a test database and MySQL in >> production. Flyway doesn't abstract SQL language differences but that has >> been okay for the most part as H2 has been compatible however for this >> particular issue I haven't found a way to handle this short of writing two >> different migrations or I wrote a Java based migration that just checks for >> the use of H2 and replaces "DROP INDEX" with "DROP CONSTRAINT". >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at https://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
