Original post - http://anothermysqldba.blogspot.com/2014/08/mysql-foreign-keys-example-error-1452.html
Así que me encontré con una situación hoy en día se trata de tener que actualizar un campo, pero el usuario no podía hacerlo debido a las restricciones de claves foráneas afines.
Esta entrada de blog con ser un ejemplo sencillo que muestra una clave externa y cómo actualizarlos si tiene que hacerlo.
En primer lugar vamos a crear una tabla simple y rellenarla con datos aleatorios.
Ahora vamos a necesitar otra tabla que tiene una clave externa atada a nuestra mesa anterior.
Así que poblamos algunos datos aleatorios en las mesas para que podamos tratar y actualizar más tarde.
post anterior sobre números aleatorios si necesita es aquí
Aceptar que es una manera indirecta para generar algunos datos aleatorios para este ejemplo.
Entonces, ¿qué sucede si necesitábamos actualizar datos relacionados con el valor de ABC en table_with_fk?
Así fue bloqueado como debería haber sido. Nosotros, después de todo tenemos "ON DELETE NO ACCIÓN SOBRE ACTUALIZACIÓN NO ACCION" en la definición de la tabla.
No todo está perdido sin embargo. Una simple modificación de la variable "FOREIGN_KEY_CHECKS" permitirá que instrucción de actualización se ejecute. Sin embargo, es más seguro para ejecutar esto, en mi opinión, dentro de una transacción.
Ahora la pregunta general es ¿por qué quieres romper sus valores de clave externa que se había puesto en su lugar por una razón?Eso es totalmente de usted.
Hoy sucedió que de alguna manera un valor había insertado con el espacio en blanco adicional en el valor en la base de datos. Así que hice una operación similar a actualizar y eliminar el espacio en blanco.
En general ... esto es sólo para mostrar que se puede hacer.
Así que me encontré con una situación hoy en día se trata de tener que actualizar un campo, pero el usuario no podía hacerlo debido a las restricciones de claves foráneas afines.
Esta entrada de blog con ser un ejemplo sencillo que muestra una clave externa y cómo actualizarlos si tiene que hacerlo.
En primer lugar vamos a crear una tabla simple y rellenarla con datos aleatorios.
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 ; Ahora vamos a necesitar otra tabla que tiene una clave externa atada a nuestra mesa 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; Así que poblamos algunos datos aleatorios en las mesas para que podamos tratar y actualizar más tarde.
post anterior sobre números aleatorios si necesita es aquí
[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 |
+---------------------------------+--------------------------+----------------+ Aceptar que es una manera indirecta para generar algunos datos aleatorios para este ejemplo.
Entonces, ¿qué sucede si necesitábamos actualizar datos relacionados con el valor de ABC en 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) Así fue bloqueado como debería haber sido. Nosotros, después de todo tenemos "ON DELETE NO ACCIÓN SOBRE ACTUALIZACIÓN NO ACCION" en la definición de la tabla.
No todo está perdido sin embargo. Una simple modificación de la variable "FOREIGN_KEY_CHECKS" permitirá que instrucción de actualización se ejecute. Sin embargo, es más seguro para ejecutar esto, en mi opinión, dentro de una transacción.
[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) Ahora la pregunta general es ¿por qué quieres romper sus valores de clave externa que se había puesto en su lugar por una razón?Eso es totalmente de usted.
Hoy sucedió que de alguna manera un valor había insertado con el espacio en blanco adicional en el valor en la base de datos. Así que hice una operación similar a actualizar y eliminar el espacio en blanco.
En general ... esto es sólo para mostrar que se puede hacer.
