Insert or Update violates foreign key constraints

[thiago_tcr]

Hi!

I recently changed my database from Google Sheets to Postgres.

I have a ITEM and a CATEGORY table where Item table has a Category column (wich is a foreign key).

Now, everytime I need to add a new Item wich I need to also add a new category:

I get a violate message:

I understand the behavior, trying to add an Item with a Category ID that does not exist in Category table.

Is there any work around this issue?

[SkrOYC]

I think that this is related to how AppSheet works.
AppSheet is trying to upload the main record first and then the ones you added through the dropdown/inner form.
Since Postgres is checking if the item is already on your Categories table and it's not when the main record is added, it's throwing an error.

Now, IMO this should be fixed/changed if it's actually working the way I explained it, adding parents before children, but I'm not sure about it.

Try changing the constraint/policy from Postgres while there is a solution

@Steve @Aleksi @Arthur_Rallu @Jonathon 

[Jonathon]

Absolutely this is what's happening 

[SkrOYC]

---

@Jonathon wrote:

Absolutely this is what's happening

---

Thanks budy! 😀

I knew you had experience with Postgres, although this may be a problem with any other SQL database

[Steve]

Don't use the database to enforce data (referential) integrity; leave it to AppSheet.

[Milko]

Hi @Steve ,

It seems if we want to have data integrity, we cannot leave it to Appsheet, since this error shows that Appsheet breaks data integrity. I guess from Appsheet's perspective it is fine to be eventual consistent, i.e. there is a point in time when a record from one table points to a missing record in another. My question is, is there a way to enforce strong consistency, i.e. use the database constraints to do that?

[Steve]

AppSheet has no support for database constraints. You could try.

[thiago_tcr]

Can´t seem to find a way to solve this rather then removing the constraint... 

Any indications?

[Jonathon]

You'll need to find a workflow in AppSheet where the parent records are created before the child records. There are generally several options here which may or may not be ideal depending on how your app works.

The easy solution is to remove the constraint.