Início / dba / Perguntas / 316935
Accepted
loris
Asked: 2022-09-16 06:20:14 +0800 CST

Como posso alterar a REFERÊNCIA em uma CHAVE ESTRANGEIRA?

  • 772

Após renomear algumas tabelas, recebo o seguinte erro:

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`))

O motivo é que a tabela associationsem REFERENCESprecisa ser alterada para users.

Qual é a maneira mais fácil de conseguir isso?

foreign-key mariadb

1 respostas

  1. Best Answer

    Você pode seguir um processo de três etapas.

    1. Identify the foreign keys names from the tables

    No seu exemplo show create table user_eventsdaria o nome da chave estrangeira.

    Você pode encontrar todos os nomes de chaves estrangeiras para um esquema usando a consulta abaixo:

    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

    Se o seu mecanismo de tabelas for Innodb , use a seguinte consulta para descartar a chave estrangeira:

    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;

    Nota . As etapas 2 e 3 podem ser combinadas em uma consulta, mas o nome da chave estrangeira deve ser diferente do descartado

    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;

    Resposta ao comentário de Rick

    Duvido que os LOCK/UNLOCKs sejam necessários. Ergest, você sabe que eles são necessários?

    Dos documentos Limitações de DDL online

    Ao executar uma operação DDL online in-loco, o encadeamento que executa a instrução ALTER TABLE aplica um log online de operações DML que foram executadas simultaneamente na mesma tabela de outros encadeamentos de conexão. Quando as operações DML são aplicadas, é possível encontrar um erro de entrada de chave duplicada (ERRO 1062 (23000): Entrada duplicada), mesmo que a entrada duplicada seja apenas temporária e seja revertida por uma entrada posterior no log online.

    O erro enfrentado sem travar a tabela (nem sempre) , na maioria das vezes acontece em tabelas grandes o que resultará na falha do comando alter :

    Erro 1062: Entrada duplicada '' para chave 'PRIMARY' Instrução SQL: ALTER TABLE user_events DROP FOREIGN KEY user_events_ibfk_1, ADD CONSTRAINT user_events_ibfk_2FOREIGN KEY ( uid) REFERENCES users ( uid);

    Para evitar isso, use a sintaxe de bloqueio adequada conforme descrito acima

    • 2

relate perguntas