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]> 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]. > 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. > -- 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.
