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