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.
