Styjun

I have a foreign key that was generated with the following command in an old and already deployed migration:

ALTER TABLE `job_template`
 ADD COLUMN `parent_id` BIGINT,
 ADD FOREIGN KEY fk_job_template_parent_id(parent_id) REFERENCES job_template(id) ON DELETE CASCADE;

Now I am trying to drop this foreign key with following command:

ALTER TABLE job_template DROP FOREIGN KEY fk_job_template_parent_id;

The problem is that this works for mariaDB but not for mySQL and I need a migration that would work in both cases

If I list the SHOW CREATE TABLE command (before the deleting of the foreign key) from both environments I get the following:

mariaDB:

 constraint fk_job_template_parent_id foreign key (parent_id) references job_template (id) on delete cascade,

mysql:

 constraint job_template_ibfk_5 foreign key (parent_id) references job_template (id) on delete cascade,

The constraint names are different in the 2 environments, and thus I have no way to write a migration that would consistently drop this foreign key.

Is there any way to get around this situation?

Your problem is that you are not explicitly naming your constraints. This leaves each database to pick a name for you. The trick here is to name your foreign key constraints explicitly, when you create the actual tables on both MySQL and MariaDB:

CREATE TABLE job_template (
 ...,
 parent_id int NOT NULL,
 CONSTRAINT your_constraint FOREIGN KEY fk_name (parent_id)
 REFERENCES job_template(id) ON DELETE CASCADE
);

But fixing your immediate situation would require more work. One option would be to query the information schema table, for the table involved, to find out the actual constraint names:

USE INFORMATION_SCHEMA;

SELECT
 TABLE_NAME,
 COLUMN_NAME,
 CONSTRAINT_NAME,
 REFERENCED_TABLE_NAME,
 REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE
 TABLE_SCHEMA = 'your_db' AND
 TABLE_NAME = 'job_template' AND
 REFERENCED_COLUMN_NAME IS NOT NULL;

This should return one record for every column and constraint. With this information, you should be able to run your current alter statements.

This is easy enough to do using a tool like Java, or something similar. If you want to do this directly from the database, then you would need dynamic SQL, which probably means writing a stored procedure.