MySQL的外鍵例：錯誤1452

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

所以，我今天跑過的情況處理有更新的字段，但用戶無法這樣做，因為相關的外鍵約束的左右。 

這個博客帖子是一個簡單的例子，說明一個外鍵，以及如何，如果你有這樣做的更新。 

首先，讓我們創建一個簡單的表，並用隨機數據填充它。 


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 ; 

現在，我們需要另外一個表，有綁在我們前面的表的外鍵。 


[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; 

因此，讓我們填充一些隨機數據插入表中，所以我們可以嘗試更高更新它們。 
如果需要對隨機數之前的職位是在這裡 


[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 | 
+---------------------------------+--------------------------+----------------+ 

確定這是一個四圍的方式來產生一些隨機的數據，這個例子。 

因此，如果我們需要更新與在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) 

因此，它被攔截喜歡它應有的水平。 我們畢竟有“ON DELETE NO ACTION ON UPDATE NO ACTION”中的表定義。 

一切都沒有，雖然丟失。 在“FOREIGN_KEY_CHECKS”變量的簡單的編輯將允許UPDATE語句來執行。 然而，它是安全執行這一點，在我看來，在一個事務中。 


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

現在整體的問題是，為什麼你要打破你已到位是有原因的設置外鍵的值？ 這完全取決於你。 

今天發生的事情，不知怎的，一個值已插入的值插入數據庫多餘的空白。 所以，我做了一個類似的交易，以更新和刪除空白。 

總體來說...這只是表明它可以做到的。