主页 / dba / 问题 / 316935
Accepted
loris
Asked: 2022-09-16 06:20:14 +0800 CST

如何更改外键中的引用？

  • 772

重命名一些表后，出现以下错误：

MariaDB [testdb]> INSERT INTO user_events (date, uid, operation, info) VALUES('2022-09-15','xyz','create',NULL);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails `testdb`.`user_events`, CONSTRAINT `user_events_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `associations` (`uid`))

原因是需要将里面的associations表REFERENCES改成users.

实现这一目标的最简单方法是什么？

foreign-key mariadb

1 个回答

  1. Best Answer

    您可以遵循三个步骤。

    1. Identify the foreign keys names from the tables

    在您的示例show create table user_events中，将给出外键名称。

    您可以使用以下查询找到架构的所有外键名称：

    SELECT CONSTRAINT_NAME,TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'world';

    2. Drop the foreign key constraint

    如果您的表引擎是Innodb ，请使用以下查询删除外键：

    SET autocommit=0;
LOCK TABLES user_events WRITE;
ALTER TABLE user_events DROP FOREIGN KEY user_events_ibfk_1;
COMMIT;
UNLOCK TABLES;

    3. Recreate the foreign key constraint

    SET autocommit=0;
LOCK TABLES user_events WRITE;
ALTER TABLE user_events  ADD CONSTRAINT `user_events_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);
COMMIT;
UNLOCK TABLES;

    注意。第 2 步和第 3 步可以合并为一个查询，但外键名称必须与删除的名称不同

    SET autocommit=0;
LOCK TABLES user_events WRITE;
ALTER TABLE user_events 
DROP FOREIGN KEY user_events_ibfk_1,
ADD CONSTRAINT `user_events_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`));
COMMIT;
UNLOCK TABLES;

    对里克评论的回应

    我怀疑是否需要锁定/解锁。Ergest，你知道他们需要吗？

    来自 docs Online DDL 限制

    运行就地在线 DDL 操作时，运行 ALTER TABLE 语句的线程应用从其他连接线程在同一个表上同时运行的 DML 操作的在线日志。应用 DML 操作时，可能会遇到重复键条目错误（ERROR 1062 (23000): Duplicate entry），即使重复条目只是临时的并且将由在线日志中的稍后条目恢复。

    在没有锁定表的情况下面临的错误（并非总是如此），大部分时间发生在大表上，这将导致 alter 语句失败：

    错误 1062：键 'PRIMARY' SQL 语句的重复条目 ''：ALTER TABLE user_events DROP FOREIGN KEY user_events_ibfk_1, ADD CONSTRAINT user_events_ibfk_2FOREIGN KEY ( uid) REFERENCES users ( uid);

    为防止这种情况，请使用上述正确的锁定语法

    • 2

相关问题