SQL:由于自动生成的约束而无法删除外键 [英] Sql: cannot drop foreign key due to auto-generated constraint

问题描述

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

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.

这篇关于SQL:由于自动生成的约束而无法删除外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！