Anyway to drop unique constraint with the same syntax between MySQL and H2?

[Ryan Rupp]

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

 

[Noel Grandin]

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 

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Ryan Rupp]

Ok thanks for the response, I logged an issue - https://github.com/h2database/h2database/issues/294