Drop H2 constraint dynamically

Question

I want to drop all the FKs in a H2 (MySQL database) table, as it is described here. I wrote the following query:

execute immediate ('alter table mytable drop constraint ' || 
QUOTE_IDENT((select "UNIQUE_INDEX_NAME" "INFORMATION_SCHEMA"."CONSTRAINTS" 
where "TABLE_NAME"='mytable' and "CONSTRAINT_TYPE"='REFERENTIAL')));

However, I run into the following exception:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "PRIMARY_KEY_A" not found; SQL statement: execute immediate ('alter table collection drop constraint ' || QUOTE_IDENT((select "UNIQUE_INDEX_NAME" from "INFORMATION_SCHEMA"."CONSTRAINTS" where "TABLE_NAME"='collection' and "CONSTRAINT_TYPE"='REFERENTIAL'))) [90057-200]

Do you have any idea why is that?

Answer 1

You cannot drop multiple constraints in one command in H2.

But you can drop one referential constraint with unknown name with the following command:

EXECUTE IMMEDIATE
    SELECT 'ALTER TABLE mytable DROP CONSTRAINT '
        || QUOTE_IDENT(CONSTRAINT_NAME)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME ILIKE 'mytable'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    FETCH FIRST ROW ONLY;

If you have more than one constraint, you need to execute this command multiple times until an exception will be thrown.