Dropping Foreign Key not supported in H2

I have a DB (MariaDB) from which I need to drop a foreign key. Nothing would be easier, as

alter table people_contacts
drop foreign key people_contacts;

perfectly do the job. Assume, of course, that the above are the names of the tables and key

But I'm here asking because of an "external" complication. Application is a Java application that uses:

Flyway to execute the DB migration
GitHub actions to run automatic tests.

Thus, during testing I run an H2 database, and apparently drop foreign key is not supported by H2. I tried playing with the H2 compatibility properties all day, with the only result of changing the error sometimes.

So I cannot create an unique migration script that should run first in H2 when embedded in test DB and then on MariaDB in the real DBs (including production). I may create different version of the migration, but I would really like to avoid this approach.

Would someone see any workaround?

Thanks in advance!

Solution

While thinking that the only way to workaround my issue is rebuilding the table, I found that this is needed for another strong reason.

In short

There's no issue or incompatibility with the drop itself, if used the standard way.

My drop command

alter table people_contacts drop foreign key people_contacts_fk;

is not SQL standard way, it should instead be

ALTER TABLE people_contacts DROP CONSTRAINT people_contacts_fk

as pointed by @evgenijryazanov in his comments. And in H2 it would work perfectly, but in my case complaints for the lack of the constraint with that name. And that was actually my issue, back in time, in the creation of the table.

In details

Investigating the constraint not found in H2 (please note H2 DB lives for a span of milliseconds and it's later destroyed), I opened the DDL creating the table and I found it was created as

create table people_contacts (
  ... fields ..., 
  foreign key (person_id) references people (id)
);

In that way the constraint is not given an explicit name, and the name it will have will be db-dependant. The name I found on my persistent DB was not explicitly given, and I didn't realized it as it was an earlier mistake not in line with the practice of the project. So (sorry!) instead of checking the original DDL i charged H2 for incompatibility.

Constraint should be created as

create table people_contacts (
  ... fields ..., 
  constraint explicit_name_for_it foreign key (person_id) references people (id)
);

in order to ensure that both in H2 and in MariaDB is created with the given name and the given name can later be used to drop it if needed.

There's of course no issue in the way the constraint works, and a similar mistake may remain undiscovered, until you need to drop such a constraint and there's the need to call it by name.

Lesson learned

Always have your constraints with an explicit name, because in the future you may need to "call them by name" to drop or alter them.

I found unfortunately some of them created erroneously back in time. The way I'm thinking of to solve the issue is:

renaming the table suffixing it as "old"
rebuilding the table with the constraints with explicit names
reinserting from the old table all the data into the new one
dropping the "old" table.