mysql - 如何使用 REFERENCED_TABLE_NAME 使此 SQL 在 H2 中工作

Question

我的情况是，删除外键约束的唯一方法是这个解决方案；

-- YOU MUST SPECIFY THESE VARIABLES TO FULLY IDENTIFY A CONSTRAINT
SET @table_name = '...';
SET @column_name = '...';
SET @referenced_table_name = '...';
SET @referenced_column_name = '...';
-- make sure to limit queries to a single db schema
SET @db_name = '...'; 

-- find the name of the foreign key and store it in a var
SET @constraint_name = (
    SELECT constraint_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = @table_name
        AND COLUMN_NAME = @column_name
        AND CONSTRAINT_SCHEMA = @db_name
        AND referenced_table_name = @referenced_table_name
        AND referenced_column_name = @referenced_column_name);

-- prepare the drop statement in a string and run it
SET @s = concat('alter table ', @table_name, ' drop foreign key ', @constraint_name);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我正在通过 Liquibase 运行这些语句。以上适用于 MySQL，但不适用于 H2。错误是，

SEVERE 2/23/21, 12:18 PM: liquibase: adaccount.sql: adaccount.sql::53::acheong: Change Set adaccount.sql::53::acheong failed.
Error: Column "REFERENCED_TABLE_NAME" not found; SQL statement:
SET @constraint_name = (
    SELECT constraint_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = @table_name
        AND COLUMN_NAME = @column_name
        AND CONSTRAINT_SCHEMA = @db_name
        AND REFERENCED_TABLE_NAME = @referenced_table_name
        AND REFERENCED_COLUMN_NAME = @referenced_column_name) [42122-196] [Failed SQL: SET @constraint_name = (
    SELECT constraint_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = @table_name
        AND COLUMN_NAME = @column_name
        AND CONSTRAINT_SCHEMA = @db_name
        AND REFERENCED_TABLE_NAME = @referenced_table_name
        AND REFERENCED_COLUMN_NAME = @referenced_column_name)]
INFO 2/23/21, 12:18 PM: liquibase: adaccount.sql::53::acheong: Successfully released change log lock

不确定我是否正在查看正确的参考，但它似乎referenced_table_name可能不是H2 中的事情。

有没有办法可以调整这个准备好的语句以同时适用于 MySQL 和 H2？

注意：外键未命名，所以我不能使用简单的ALTER TABLE ... DROP FOREIGN KEY. 我也无法使用dropForeignKeyContraint，因为我坚持使用非 XML、仅限 SQL 的配置。我也研究了其他几种方法——不幸的是，上述解决方案中的用户是完全正确的——没有其他方法可以工作。

---

编辑

感谢@EvgenijRyazanov，我走得更远了。我升级到 H2 1.4.200 以便EXECUTE IMMEDIATE可用，并加入INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. 内部查询在 MySQL 中按预期工作：

然而，H2 中的相同内部查询似乎返回 0 结果（空字符串）：

Syntax error in SQL statement "ALTER TABLE audit_event DROP FOREIGN KEY [*]"; expected "identifier"; SQL statement:
ALTER TABLE audit_event DROP FOREIGN KEY  [42001-200] [Failed SQL: EXECUTE IMMEDIATE @s]

标签： mysqlforeign-keysh2liquibaseinformation-schema