B
As you say https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html When creating your foreign key, you can tell him what your behavior will be if the reference changes.The reference 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 reference action on it, RESTRICT is assumed by default. 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 to PAISES, you have to explicitly tell him 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 changing the referenced table because it puts you on the safest stage.It seems to me that, for your use case, the only thing that serves you is to make UPDATE in the PAISES table, since a replace will always erase the row and insert another one, losing the reference integrity in the way. Likewise, you should define your key 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;
As you want to replace values, I imagine you don't 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 do now withREPLACE 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 it.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 greater abundanceWhen I say:a replace will always delete the row and insert another,
losing in the way the referenceal integrityI mean, if you allow the deletion of a record that is referenced by another table, you'd have to define that ON DELETE that board erase the row ON DELETE CASCADE (what you obviously don't want) or leave that field like null. ON DELETE SET NULL. The latter will retain the row or record in the table PERSONASbut that person will be left without nationality and that registration will not regain his nationality even if you insert the original country again. Simply, by leaving it in NULL, it is delinked from the referenced table. The table PERSONAS It can't have records with countries that don't exist on the table, but it could have records without a country, and that, as I said, is going to kill reference integrity until at a time no person has a country.
