1

We are using AWS RDS for mariadb. There are several tables out of which I am mentioning two which are causing the issue:

Table 1: PRODUCT_OFFERING

Table 2: CHARACTERISTIC

Related by foreign key as below:

foreign_table rel primary_table constraint_name fk_columns
eshop_preprod_cart_plh.CHARACTERISTIC -> eshop_preprod_cart_plh.PRODUCT_OFFERING FK_PRODUCT_OFFERING_ID_CHARACTERISTIC_TO_ID_PRODUCT_OFFERING PRODUCT_OFFERING_ID

enter image description here

When we were trying to delete entry from PRODUCT_OFFERING Table, with the below query:

delete from PRODUCT_OFFERING where id = "2c9080e988941ecb0188944b8d430024";

we got the below error :

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (eshop_preprod_cart_plh.CHARACTERISTIC, CONSTRAINT FK_PRODUCT_OFFERING_ID_CHARACTERISTIC_TO_ID_PRODUCT_OFFERING FOREIGN KEY (PRODUCT_OFFERING_ID) REFERENCES PRODUCT_OFFERING (ID))
0.411 sec

The constraint has been created with no action and hence the default action would be RESTRICT. This is understood.

CONSTRAINT `FK_PRODUCT_OFFERING_ID_CHARACTERISTIC_TO_ID_PRODUCT_OFFERING` FOREIGN KEY (`PRODUCT_OFFERING_ID`) REFERENCES `PRODUCT_OFFERING` (`ID`),

However, we checked the corresponding entry in characteristic table,

select * from CHARACTERISTIC where PRODUCT_OFFERING_ID = '2c9080e988941ecb0188944b8d430024';

We got no such entry for the given PRODUCT_OFFERING_ID and hence ideally the database should allow such entry to be deleted. We were not able to understand what could be the issue.

We then tried to drop the foreign key on column PRODUCT_OFFERING_ID from characteristic table and created the foreign key again. After this we were able to delete the entry in PRODUCT_OFFERING TABLE with no changes done in any row of any table.

We have been able to get away with the issue that we were facing, but in order to be aware for any such issue, is there a possibility for the for foreign key data to become corrupt which would make the database give wrong results?

We were able to find one such issue online in which another user had faced same issue and fixed the issue just like this way. MariaDB: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

1
  • Please provide SHOW CREATE TABLE for each of the two tables.
    – Rick James
    Sep 20, 2023 at 18:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.