Thursday, August 14, 2014

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) 


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

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

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