Sql: cannot drop foreign key due to auto-generated constraint

Question

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?

Answer 1

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.