Replace into MySql problem with foreign key

Question

Replace into MySql problem with foreign key

Navigation

#1 by (3 votes)

2

I'm doing an insert in mysql and searching the internet I found the replace into that if the record exists it deletes it and enters the new one, now try it in mysql and it gave me a foreign key error since the registry is associated to another table, as the structure of the foreign key must be so that the replace into can be executed.

Error: # 1451 - Can not delete or update parent row: a foreign key constraint fails ( u261045981_app . PERSONAS , CONSTRAINT PERSONAS_ibfk_1 FOREIGN KEY ( PA_PAIS_ID ) REFERENCES PAISES ( PA_PAIS_ID ))

I insert the insert into a table called countries that is associated with people

Table structure

CREATE TABLE 'PAISES' (
'PA_PAIS_ID' int(11) NOT NULL,
'PA_NOMBRE' varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
'PA_MONEDA' varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
'PA_CODIGO_ISO' varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
'PA_DECIMALES' smallint(6) DEFAULT NULL,
'PA_SEPARADOR_DECIMAL' char(1) COLLATE utf8_unicode_ci DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
 COMMENT='Paises en los que estara disponible la aplicacion';

 ALTER TABLE 'PAISES'
 ADD PRIMARY KEY ('PA_PAIS_ID');

 CREATE TABLE 'PERSONAS' (
'PE_USU_ID' int(11) NOT NULL COMMENT 'id incremental del usuario',
'PA_PAIS_ID' int(11) DEFAULT NULL COMMENT 'FK tabla pais, permite 
 asociar pais al usuario',
'PE_NOMBRE' varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
'PE_CORREO' varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 
 'correo de la persona, campo unico y usuario que se pide al iniciar 
 sesion',
 'PE_CLAVE' varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 
  clave del usuario',
 'PE_ESTADO' smallint(6) DEFAULT NULL COMMENT 'estado del usuario, 1 
  es activo y 0 es inactivo y no puede ingresar a la aplicacion',
  'PE_FECHA_CREACION' timestamp NULL DEFAULT current_timestamp() ON 
  UPDATE current_timestamp() COMMENT 'fecha en la cual se crea la 
  cuenta del usuario'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
 COMMENT='Tabla donde se almacena los datos de los usuarios';


 ALTER TABLE 'PERSONAS'
 ADD PRIMARY KEY ('PE_USU_ID'),
 ADD KEY 'PA_PAIS_ID' ('PA_PAIS_ID');

 ALTER TABLE 'PERSONAS'
 ADD CONSTRAINT 'PERSONAS_ibfk_1' FOREIGN KEY ('PA_PAIS_ID') 
 REFERENCES 'PAISES' ('PA_PAIS_ID');

Finally the insert to table countries

REPLACE INTO 'PAISES' ('PA_PAIS_ID', 'PA_NOMBRE', 'PA_MONEDA','PA_CODIGO_ISO', 'PA_DECIMALES', 'PA_SEPARADOR_DECIMAL') 
VALUES  (1, 'Chile', 'Peso Chileno', 'Clp', 0, NULL);

mysql insert

asked by jose miguel jara 21.10.2018 в 20:33

source

1 answer

How to perform date conversion YYYY-mm-dd in bash How to show only a range of values on the highcharts Y axis

score 3 · Accepted Answer

As it says MySQL documentation , when creating your foreign key can tell you what your behavior will be if you change the reference.

The referential action can be:

ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
ON UPDATE [RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]

If you do not put any referential action on it, by default RESTRICT is assumed. Therefore, your statement

ALTER TABLE 'PERSONAS'
ADD CONSTRAINT 'PERSONAS_ibfk_1' FOREIGN KEY ('PA_PAIS_ID') 
REFERENCES 'PAISES' ('PA_PAIS_ID');

Equivalent to

ALTER TABLE 'PERSONAS'
ADD CONSTRAINT 'PERSONAS_ibfk_1' FOREIGN KEY ('PA_PAIS_ID') 
REFERENCES 'PAISES' ('PA_PAIS_ID')
ON UPDATE RESTRICT
ON DELETE RESTRICT;

If you want to allow modifications in COUNTRIES, you have to tell them explicitly what you want to happen when you make the modification. While that action - by default - is RESTRICT what you see now will happen. The MySQL engine prevents you from modifying the referenced table because it puts you on the safest stage.

It seems to me that, for your use case, the only thing that works for you is to do UPDATE in the COUNTRY table, since a replace will always erase the row and insert another one, losing the referential integrity on the way. For the same reason, you should define your password as:

ALTER TABLE 'PERSONAS'
ADD CONSTRAINT 'PERSONAS_ibfk_1' FOREIGN KEY ('PA_PAIS_ID') 
REFERENCES 'PAISES' ('PA_PAIS_ID')
ON UPDATE CASCADE
ON DELETE RESTRICT;

Since you want to replace values, I guess you do not know a priori if the row exists, and that's why you use replace to avoid inserting a row that already exists. In that case, what you now do with

REPLACE INTO 'PAISES' ('PA_PAIS_ID', 'PA_NOMBRE', 'PA_MONEDA','PA_CODIGO_ISO', 'PA_DECIMALES', 'PA_SEPARADOR_DECIMAL') 
VALUES  (1, 'Chile', 'Peso Chileno', 'Clp', 0, NULL);

You can do it with

INSERT INTO 'PAISES' ('PA_PAIS_ID', 'PA_NOMBRE', 'PA_MONEDA','PA_CODIGO_ISO', 'PA_DECIMALES', 'PA_SEPARADOR_DECIMAL') 
VALUES  (1, 'Chile', 'Peso Chileno', 'Clp', 0, NULL)
ON DUPLICATE KEY UPDATE 
PA_PAIS_ID=1,
PA_NOMBRE='Chile',
PA_MONEDA='Peso Chileno',
PA_CODIGO_ISO='Clp',
PA_DECIMALES=0,
PA_SEPARADOR_DECIMAL=null;

Edit, for more details

When I say that:

a replace will always delete the row and insert another, losing referential integrity on the way

I mean that if you allow the deletion of a record that is referenced by another table, you would have to define that ON DELETE that table delete row ON DELETE CASCADE (what you obviously do not want) or leave that field as null ON DELETE SET NULL The latter will keep the row or record in the PERSONAS table, but that person will be without a nationality and that record will not recover their nationality even if you re-insert the original country. Simply by leaving it in NULL, it is unlinked from the referenced table. The table PERSONAS can not have records with countries that do not exist in the referenced table, but it could have records without country, and that -as I said- you will be killing the referential integrity until at a moment no one has a country .