Foreign key constraint error when re-uploading edited JSON submissions with modified household_id

[tosmartak]

Dear Formshare development team,

I am encountering a foreign key constraint error when attempting to re-upload edited submission records, and I would appreciate guidance on the correct way to handle this case.

Context

• I have an existing form already uploaded to Formshare.
• This form is a dietary recall, where household_id values are pulled from a pre-existing lookup table.
• I later realized that I failed to create a recall-specific household identifier to control for duplicate recalls per household.
• A small number of submissions were already collected before this issue was identified.

What I attempted

1. Some of the affected submissions were flagged as errors due to duplicated household_id values.
2. To address this, I edited the affected submission JSON files locally by adjusting the household_id values, with the intention of resolving duplicates during downstream data cleaning.
3. This approach has worked for me in the past with other forms.
4. I then attempted to re-upload the edited JSON files back into the repository.

Observed error

When attempting to push the edited records back into the repository, I consistently receive the following error:

Cannot add or update a child row: a foreign key constraint fails (FS_c2b8c5ff_3421_4cfe_b236_a7c663289c22.maintable, CONSTRAINT fk_fc94310c_ff26_43ce_a2ff_fb111c1cd187 FOREIGN KEY (household_id) REFERENCES lkphousehold_id (household_id_cod))

This occurred for the first two edited records I tried to re-upload.
A screenshot of the error is attached below.

Expectation / question

My expectation was that correcting the household_id values in the JSON file would allow the records to be pushed back into the repository, as I have done previously.

Could you please advise:

• Whether editing household_id directly in the submission JSON is supported when the field is linked to a lookup table in this case (which has worked for me before)
• If not, what is the recommended workflow for correcting already-submitted records in this scenario
• Whether there is a way to reconcile or update lookup-linked values without violating the foreign key constraint

Thank you very much for your assistance.

[FormShareDev]

Hi @tosmartak,

Do the household IDs come from a CSV? Those cases happen when the household ID is not present in lkphousehold_id.

Which server is experiencing this problem? Alliance or the Community Server?

[tosmartak]

Hello @FormShareDev,

Yes, the household IDs are coming from a csv file.

I also tried to see if adding the JSON adjusted household IDs to the csv file before pushing back to the repository would work, but it didn't.

Yes, it is the Alliance server.

In the meantime, I have downloaded all the affected JSON files and appended them to the downloaded repository data locally so I can forge ahead.

However, it would be good to know if there is any internal solution on formshare for this issue for future cases.

Thanks.