So I ran across a situation today dealing with having to update a field but the user was unable to do so because of the related foreign key constraints.
This blog post with be a simple example showing a foreign key and how to update them if you have to do so.
First let us create a simple table and populate it with random data.
Now we will need another table that has a foreign key tied to our previous table.
So let us populate some random data into the tables so we can try and update them later.
previous post on random numbers if needed is here
OK that is a round about way to generate some random data for this example.
So what happens if we needed to update data related to the ABC value in table_with_fk?
So it was blocked like it should have been. We do after all have "ON DELETE NO ACTION ON UPDATE NO ACTION" in the table definition.
All is not lost though. A simple edit of the "FOREIGN_KEY_CHECKS" variable will allow that update statement to execute. However, It is safer to execute this , in my opinion, within a transaction.
Now the overall question is why would you want to break your foreign key values that you had set in place for a reason? That is entirely up to you.
Today it happened that somehow a value had been inserted with extra white space in the value into the database. So I did a similar transaction to update and remove the white space.
Overall ... this is just to show it can be done.
This blog post with be a simple example showing a foreign key and how to update them if you have to do so.
First let us create a simple table and populate it with random data.
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 ;
Now we will need another table that has a foreign key tied to our previous table.
[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;
So let us populate some random data into the tables so we can try and update them later.
previous post on random numbers if needed is here
[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 that is a round about way to generate some random data for this example.
So what happens if we needed to update data related to the ABC value in 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)
So it was blocked like it should have been. We do after all have "ON DELETE NO ACTION ON UPDATE NO ACTION" in the table definition.
All is not lost though. A simple edit of the "FOREIGN_KEY_CHECKS" variable will allow that update statement to execute. However, It is safer to execute this , in my opinion, within a transaction.
[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)
Now the overall question is why would you want to break your foreign key values that you had set in place for a reason? That is entirely up to you.
Today it happened that somehow a value had been inserted with extra white space in the value into the database. So I did a similar transaction to update and remove the white space.
Overall ... this is just to show it can be done.
