MySQL Foreign Chaves Exemplo & ERROR 1452

Original post - http://anothermysqldba.blogspot.com/2014/08/mysql-foreign-keys-example-error-1452.html

 Então, eu encontrei uma situação hoje lidar com a necessidade de atualizar um campo, mas o usuário não foi capaz de fazê-lo por causa das restrições de chave estrangeira relacionados. 

Este blog com ser um exemplo simples que mostra uma chave estrangeira e como atualizá-los se você tiver que fazê-lo. 

Primeiro vamos criar uma tabela simples e preenchê-lo com dados aleatórios. 


CREATE TABLE `table_w_code` ( 
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
`NameofCode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
PRIMARY KEY (`SOMECode`) 
) ENGINE=InnoDB ; 

Agora vamos precisar de outra tabela que tem uma chave estrangeira ligada à nossa tabela anterior. 


[anothermysqldba]> CREATE TABLE `table_with_fk` ( 
`SOMEID` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, 
`Somemorefields` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, 
PRIMARY KEY (`SOMEID`,`SOMECode`), 
KEY `FK_Patient_Facility` (`SOMECode`), 
CONSTRAINT `FK_CODE` FOREIGN KEY (`SOMECode`) REFERENCES `table_w_code` (`SOMECode`) ON DELETE NO ACTION ON UPDATE NO ACTION 
) ENGINE=InnoDB; 

Por isso, vamos preencher alguns dados aleatórios nas tabelas para que possamos tentar e atualizá-los mais tarde. 
post anterior sobre números aleatórios, se necessário é aqui 


[anothermysqldba]> SET @A = 3; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> SET @B = 15 - @A; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> SET @C = 16; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> SET @D = 25 - @C; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> INSERT INTO table_w_code VALUES 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'ABC' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'DEF' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'GHI' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'JKL' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'MNO' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'PQR' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'STU' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'VWX' ) , 
-> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) , 'YZ' ) ; 
Query OK, 9 rows affected (0.05 sec) 
Records: 9 Duplicates: 0 Warnings: 0 

[anothermysqldba]> SELECT * from table_w_code ORDER BY NameofCode; 
+--------------------------+------------+ 
| SOMECode | NameofCode | 
+--------------------------+------------+ 
| 204e9800998ecf8427e | ABC | 
| f00b204e9800998e | DEF | 
| 98f00b204e9800998ecf8427 | GHI | 
| 98f00b204e9800998e | JKL | 
| 1d8cd98f00b204e9800 | MNO | 
| 1d8cd98f00b204e9800998ec | PQR | 
| 0b204e9800998ecf8427e | STU | 
| cd98f00b204e9800998ec | VWX | 
| d98f00b204e9800998ecf842 | YZ | 
+--------------------------+------------+ 
9 rows in set (0.00 sec) 

[anothermysqldba]> SET @D = 2; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> SET @E = 25 - @D; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> INSERT INTO table_with_fk SELECT SUBSTR(md5(''),FLOOR( @D + (RAND() * @E ))), SOMECode , NameofCode FROM table_w_code; 
Query OK, 9 rows affected (0.08 sec) 
Records: 9 Duplicates: 0 Warnings: 0 

[anothermysqldba]> select * from table_with_fk ORDER BY Somemorefields; 
+---------------------------------+--------------------------+----------------+ 
| SOMEID | SOMECode | Somemorefields | 
+---------------------------------+--------------------------+----------------+ 
| 41d8cd98f00b204e9800998ecf8427e | 204e9800998ecf8427e | ABC | 
| e9800998ecf8427e | f00b204e9800998e | DEF | 
| 98ecf8427e | 98f00b204e9800998ecf8427 | GHI | 
| 00b204e9800998ecf8427e | 98f00b204e9800998e | JKL | 
| 04e9800998ecf8427e | 1d8cd98f00b204e9800 | MNO | 
| 04e9800998ecf8427e | 1d8cd98f00b204e9800998ec | PQR | 
| b204e9800998ecf8427e | 0b204e9800998ecf8427e | STU | 
| b204e9800998ecf8427e | cd98f00b204e9800998ec | VWX | 
| 4e9800998ecf8427e | d98f00b204e9800998ecf842 | YZ | 
+---------------------------------+--------------------------+----------------+ 

OK, que é um círculo sobre a maneira de gerar alguns dados aleatórios para este exemplo. 

Então o que acontece se precisávamos para atualizar dados relacionados com o valor ABC em table_with_fk? 


[anothermysqldba]> SELECT SOMEID , SOMECode , Somemorefields FROM table_with_fk WHERE Somemorefields = 'ABC'; 
+---------------------------------+---------------------+----------------+ 
| SOMEID | SOMECode | Somemorefields | 
+---------------------------------+---------------------+----------------+ 
| 41d8cd98f00b204e9800998ecf8427e | 204e9800998ecf8427e | ABC | 
+---------------------------------+---------------------+----------------+ 

[anothermysqldba]> SELECT SOMECode , NameofCode FROM table_w_code WHERE NameofCode = 'ABC'; 
+---------------------+------------+ 
| SOMECode | NameofCode | 
+---------------------+------------+ 
| 204e9800998ecf8427e | ABC | 
+---------------------+------------+ 

[anothermysqldba]> 
UPDATE table_with_fk SET SOMEID = 'I UPDATED THIS' , SOMECode = 'I UPDATED THIS' WHERE SOMECode = '204e9800998ecf8427e'; 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails(`anothermysqldba`.`table_with_fk`, CONSTRAINT `FK_CODE` FOREIGN KEY (`SOMECode`) REFERENCES `table_w_code` (`SOMECode`) ON DELETE NO ACTION ON UPDATE NO ACTION) 

Por isso, foi bloqueado como deveria ter sido. Nós depois de tudo ter "ON DELETE NO ACTION ON UPDATE NO ACTION" na definição da tabela. 

Nem tudo está perdido, porém. A simples edição da variável "FOREIGN_KEY_CHECKS" permitirá que a declaração de atualização para executar. No entanto, é mais seguro para executar isso, na minha opinião, dentro de uma transação. 


[anothermysqldba]> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> SET FOREIGN_KEY_CHECKS=0; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> UPDATE table_with_fk SET SOMEID = 'I UPDATED THIS' , SOMECode = 'I UPDATED THIS' WHERE SOMECode = '204e9800998ecf8427e'; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

[anothermysqldba]> SET FOREIGN_KEY_CHECKS=1; 
Query OK, 0 rows affected (0.00 sec) 

[anothermysqldba]> COMMIT; 
Query OK, 0 rows affected (0.07 sec) 

Agora, a pergunta geral é por que você iria querer quebrar seus valores de chave estrangeira que tinha fixado no local por uma razão? É inteiramente até você. 

Hoje aconteceu que de alguma forma um valor havia sido inserido com espaço em branco extra no valor no banco de dados. Então eu fiz uma transação semelhante para atualizar e remover o espaço em branco. 

No geral ... isto é só para mostrar que pode ser feito.