Cannot add or update a child row: a foreign key constraint fails

Help me!
1

Hi,

We have following relationships defined:

Address.associate = (models) => {
    Address.belongsTo(models.Country, {
      foreignKey: "country",
      targetKey: "iso",
    });
    Address.belongsTo(models.Person);
};

Country.hasMany(models.Address, {
      foreignKey: "country",
      sourceKey: "iso",
});

When we try to update the country of an address or add an address to a country in forest we have following error:

SequelizeForeignKeyConstraintError: Cannot add or update a child row: a foreign key constraint fails (`ElypsPersonsDb`.`Address`, CONSTRAINT `Address_ibfk_1` FOREIGN KEY (`country`) REFERENCES `Country` (`iso`) ON DELETE SET NULL ON UPDATE CASCADE)

Expected behavior

We exepect to be able to add address to country and link addresses with country.

Actual behavior

Error: Cannot add or update a child row: a foreign key constraint fails

Failure Logs

e[31mSequelizeForeignKeyConstraintError: Cannot add or update a child row: a foreign key constraint fails (ElypsPersonsDb.Address, CONSTRAINT Address_ibfk_1 FOREIGN KEY (country) REFERENCES Country (iso) ON DELETE SET NULL ON UPDATE CASCADE)e[39m

Context

Please provide any relevant information about your setup.

  • Package Version: 6.3.6
  • Express Version: 4.16.3
  • Sequelize Version: 5.15.1
  • Database Dialect: MySql
  • Database Version: 5.6
  • Project Name: Elyps
2

Hi @Cyril_Limam,

I do reproduce your issue. Let me find why this happen :thinking:

1 Like
3

I created a ticket for that issue, you can track it here:

1 Like
4

Hi @Cyril_Limam,

I just finished investigating the issue you reported and it seems that as is, ForestAdmin does not handle foreignKeys pointing at another field but the primaryKey.

That means that it is a brand new thing for us to handle that will require technical specs and prioritization.

However, if this is blocking for you, I managed to craft a workaround that would allow you to create and update your records. But it is to be noted that you’ll loose the benefits from the belongsTo: no more links to countries and hasMany from addresses to countries.

Let me know if you’d be interested in such a workaround or if you prefer to keep the advantages from the belongsTo/hasMany for now.

Best regards

1 Like
5

Hi,

Thanks for your feedback. Do you have any view on when this will be supported? Meanwhile, can you share the workaround?

Best regards,
Cyril

6

Hi @Cyril_Limam,

I pushed your issue to our product team but unfortunately, I cannot commit on any potential release date for now.

As for the workaround, here are the 7 steps to follow:

Disclaimer: For this example I used countries and addresses as the table and model names you defined and country_iso as the field name in DB. Feel free to replace those with the one you are using if needed.This workaround is crafted to allow creation and edition of the countryIso field on addresses table. However the country iso field won’t be displayed as link anymore on the addresses collection.

1 - Manually add field

2 - Remove belongsTo association from the models/addresses file and the hasMany association from the models/countries.js file

3 - Add the following get route on the routes/countries.js file:

router.get('/countries-iso', permissionMiddlewareCreator.list(), async (request, response, next) => {
  const allCountries = await countries.findAll({ attributes: ['iso'], order: [['iso', 'ASC']], });
  const data = allCountries.map(country => country.iso);
  response.send({ data });
});

3b - Restart your server

4 - Go to the isoCountry field settings

5 - Define the edit widget to Dropdown then click dynamic and add /forest/countries-iso as Dataset URL

6 - Hitting the verify button should display a list of iso looking like this:

{
  "data": [
    "FR",
    "IT",
    "UK",
    "US"
  ]
}

7 - You can now close the panel and hit the save button on the right

Feel free to come back on this post if you have any questions or issue with the workaround.

Best regards

2 Likes
7

Thanks for the workaround. When is the fix planned in forest to add support for this use case?

8

Hello @Cyril_Limam, as @anon37102731 stated, we pushed your issue to our product team, but we cannot commit on any potential release date for now.
Hope you understand !