Sql:由于自动生成的约束，无法删除外键

2022-01-15 00:00:00 foreign-keys mariadb mysql

我有一个在旧的且已部署的迁移中使用以下命令生成的外键:

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;

问题是这适用于 mariaDB 但不适用于 mySQL，我需要在这两种情况下都适用的迁移

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

如果我从两个环境中列出 SHOW CREATE TABLE 命令(在删除外键之前)，我会得到以下信息:

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?

推荐答案

你的问题是你没有明确地命名你的约束.这使每个数据库都可以为您选择一个名称.这里的技巧是在 MySQL 和 MariaDB 上创建实际表时明确命名外键约束:

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;

这应该为每一列和约束返回一条记录.有了这些信息，您应该能够运行当前的 alter 语句.

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

使用 Java 之类的工具或类似的工具很容易做到这一点.如果您想直接从数据库执行此操作，则需要动态 SQL，这可能意味着编写存储过程.

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.

相关文章