insert statement conflicted with the foreign key constraint

hello uhm i wanted to test the app for Todo but when i try saving the Todo it gives me this error  can anyone help me solve this problem please"

This is a database error. You're trying to create a record (Post) with an invalid UserId. The Foreign Key in this column/attribute doesn't let you enter values not matching the User table/entity.

Review/debug your logic in order to find out why UserId has an invalid value.

Hello. How did you get the Todo app? Did you create it?

This error is a database error. Most likely, you're trying to insert a row in the database without having a UserId, but the attribute is mandatory.

leonardo.fernandes wrote:

Hello. How did you get the Todo app? Did you create it?

This error is a database error. Most likely, you're trying to insert a row in the database without having a UserId, but the attribute is mandatory.

Hi, I'm new to outsystems and I'm trying to create the ToDo App. I'm getting the same error, how can I possibly solve this? Thank you!


I am very new to OutSystems.
Tried out to build the ToDo App as given in the online exercises.
Want to proceed BUT this message prevents me from continuing.
Could some one please give a step by step explanation of how I resolve this error?

Hi Johan,

It seems that when saving your entity record (TODO), you have a (mandatory) empty attribute (TODO.Priority_Id). Setting the attribute as non mandatory or assigning a value prior to saving the data would technically fix the issue (depending on what you're trying to do).

In this scenario, Priority_Id is a foreign key to entity/table Priority. Since OutSystems uses an underlying database, these concepts are still relevant. Ready more about foreign keys here:

https://www.w3schools.com/sql/sql_foreignkey.asp

Paulo Ramos wrote:

Hi Johan,

It seems that when saving your entity record (TODO), you have a (mandatory) empty attribute (TODO.Priority_Id). Setting the attribute as non mandatory or assigning a value prior to saving the data would technically fix the issue (depending on what you're trying to do).

In this scenario, Priority_Id is a foreign key to entity/table Priority. Since OutSystems uses an underlying database, these concepts are still relevant. Ready more about foreign keys here:

https://www.w3schools.com/sql/sql_foreignkey.asp

Hi Paulo
Thanks for answering!!
As I said I am very new to OutSystems.
My plan is to work through the online exercises (do exactly as shown in the pdf's) and then return a second time to then understand what I am doing.
Unfortunately this error prevents me from continuing.

I have run the debugger function to try and locate the problem and then to try and change "the attribute as non mandatory or assigning a value prior to saving the data" BUT the pop up's do not allow me to change anything.

I have located the SQL statement where I recognize the error message references:
 

Would this help you to assist me?

Is there not an error on the DB YCGZX8041 where the ID mismatch occur?
I assume this DB is on the OutSystems server.

Hi Johan,

I don't quite remember the ToDo app (so keep in mind I don't have the business requirements), but let me further explain those two options (the one to use depends on the business requirements).

A - Setting the attribute as non mandatory

In your TODO entity, locate the Priority_Id attribute. Change the Is Mandatory property to No. This should allow you to save this record with an empty (null) value for Priority_Id (so, you'll have TODOs without a priority defined).

Side note: if you face the same error after doing this change, then it's a slightly different situation: you're assigning Priority_Id a value that doesn't exist in the Priority.Id entity/table.

B - Assigning a value prior to saving the data

In the screen / action triggering the runtime error, locate the CreateTODO or CreateOrUpdateTODO action that you're using to save the TODO record. 

The exact details depend on how you're getting the data to be saved, but for example, if you're saving the content of a Form, you're providing a Source to the mentioned action - something similar to MyForm.Record.TODO.

Then...

Option B.1: If you want to assign a default priority, then use an Assign node before the Create* action, with something like:

MyForm.Record.TODO.Priority_Id = value

(If Priority is a Static Entity, value can be similar to: Entities.Priority.Normal, depending on how it's defined.)

Option B.2: If you want the user to select a priority, you probably need a combobox or something else in the UI. Then validate if the user selected a value prior to saving (I believe some online lessons cover validations).

Hope this helps.

Hi MUSGADO,

First, delete all the record from table 2. From the error, I can get that the 1st table is user table. In table 2 there is referential integrity with user table. Please delete the userId identifier from table 2. Then deploy the Application. Then again add the user id in table 2 as a user identifier. It will work fine.  waiting for your comments.


Thank you,

Sudip

Paulo Ramos wrote:

Hi Johan,

I don't quite remember the ToDo app (so keep in mind I don't have the business requirements), but let me further explain those two options (the one to use depends on the business requirements).

A - Setting the attribute as non mandatory

In your TODO entity, locate the Priority_Id attribute. Change the Is Mandatory property to No. This should allow you to save this record with an empty (null) value for Priority_Id (so, you'll have TODOs without a priority defined).

Side note: if you face the same error after doing this change, then it's a slightly different situation: you're assigning Priority_Id a value that doesn't exist in the Priority.Id entity/table.

B - Assigning a value prior to saving the data

In the screen / action triggering the runtime error, locate the CreateTODO or CreateOrUpdateTODO action that you're using to save the TODO record. 

The exact details depend on how you're getting the data to be saved, but for example, if you're saving the content of a Form, you're providing a Source to the mentioned action - something similar to MyForm.Record.TODO.

Then...

Option B.1: If you want to assign a default priority, then use an Assign node before the Create* action, with something like:

MyForm.Record.TODO.Priority_Id = value

(If Priority is a Static Entity, value can be similar to: Entities.Priority.Normal, depending on how it's defined.)

Option B.2: If you want the user to select a priority, you probably need a combobox or something else in the UI. Then validate if the user selected a value prior to saving (I believe some online lessons cover validations).

Hope this helps.

Hallo Paulo

Thank you so much for your patience!!

I have made the change "Change the Is Mandatory property to No." .....and got the same error message.
So my problem definitely lies : "you're assigning Priority_Id a value that doesn't exist in the Priority.Id entity/table."

I will now retrace my steps in the online PDF and see if I can find the mistake of generating a non existent Priority.Id.

Will keep you posted!!


Thanks 


Sudip Dey wrote:

Hi MUSGADO,

First, delete all the record from table 2. From the error, I can get that the 1st table is user table. In table 2 there is referential integrity with user table. Please delete the userId identifier from table 2. Then deploy the Application. Then again add the user id in table 2 as a user identifier. It will work fine.  waiting for your comments.


Thank you,

Sudip

Hi Johan Strydom,

You can repeat the process which I told you. it will resolve your issues


Sudip Dey wrote:

Sudip Dey wrote:

Hi MUSGADO,

First, delete all the record from table 2. From the error, I can get that the 1st table is user table. In table 2 there is referential integrity with user table. Please delete the userId identifier from table 2. Then deploy the Application. Then again add the user id in table 2 as a user identifier. It will work fine.  waiting for your comments.


Thank you,

Sudip

Hi Johan Strydom,

You can repeat the process which I told you. it will resolve your issues


Hallo Sudip

I understand that Table 2 you refer to is the ToDo list table.
Currently there is no data in the (ToDo) table.


The User Table used in this routine is from the OutSystems System and only have two entries with UserId 2 and UserId 5. (Which I find strange that it does not number the Users from 1 onward.)

So I cannot delete the UserId's from my side as requested by you.


I am going to restart the whole exercise and see what happens then.








Hi Johan,

Since you are doing the tutorial, why not give us more info on where you are and what insert is failing?


My guess would be you are trying to insert a localtodo but your localpriority is empty ( failed the sync?).

Can you check if your local tables have data?

Hi Stefano

I have completed all the steps up to the end of page 34 of "4.4  List and Detail Screens Exercise".

All went well until I wanted to test the app in the simulator on the browser. 

When I wanted to store a new ToDo the above error appeared.


Can you check if your local tables have data? : If you refer to Local Storage.....I do not have any entities defined here.


Regarding the Data:






Johan,

can you

  • share the details of your priority button group and of all button group items (or check again that they are exactly like described on page 24)
  • share the details of the RunServerAction of CreateOrUpdateToDo, specifically, what is the source record you pass into it.
  • Set a breakpoint at beginning of SaveOnClick Action, start debugging, and share details about values of what you pass into the CreateOrUpdateToDo

Or better yet, just share your oml, because now we're just guessing...

Dorine

Hi Dorine

Details of my Priority Group Button


Button Group Item 1

Button Group Item 2


Button Group Item 3


RunServerAction of CreateOrUpdateToDo



  • Set a breakpoint at beginning of SaveOnClick Action, start debugging, and share details about values of what you pass into the CreateOrUpdateToDo

Or better yet, just share your oml, because now we're just guessing...

Sorry Dorine......do not know how to get the values or the oml at that point!!

Hi Johan,

This might seem silly but sometimes we make simple mistakes we don't even realize:

When filling out your to-do (on the simulator) are you pressing any of the button group's buttons?

You need to have selected one of the priorities (as it is mandatory according to the exercise) when you create a Todo (unless you followed Paulo's option A).

Hope this helps!

Hi Jorge

Thanks for that!

Yes I have selected one of the options....different to the previous one each time I ran a trial or the debugger,


Not winning at this stage!!

Johan,

Almost there.

Now please post the assign in your buttons action, causing the error.

Johan,

Was worth the shot! ;-)

In that case, please provide the OML Dorine was mentioning (the OML is the "source code" of your module). Go to the Module menu, there's an Export menu option, and inside an option to save the module (or you can just press Ctrl-S). If you can share it here, it'll be easier to pinpoint what's the issue you're facing.

Thanks to all for your patience and support.

I really appreciate this!!

I have attached the OML file.


Thanks Johan,

could you also attach the ToDo_Core_JCS.oml, so we can install and debug


Dorine

Sure!

Ok,

I spotted your problem, it's not in your code, but in your datamodel.


You have defined the todo identifier as a foreign key to priority identifier, instead of an id in it's own right.


So the tricky bit is how to fix this, because often once published, it can be hard to change database structures involving primary keys.  As you don't have any data yet in your database that you need to preserve, easiest for you would be to

1) make sure you have the oml files exported for both modules (which you have, they are in this post)

2) remove the entire ToDo application from your development environment

3) use db cleaner component to remove the underlying database tables

4) open the Core module oml with service studio, but don't publish right away

5) change the definition of the ToDo.Id to be LongInteger and AutoNumber

6) publish the core module

7) open the UI module, refresh all dependencies and publish

8) test


Dorine

Well spotted by Dorine.

I'd just add that, in order to avoid deleting the whole application, you can:

  • Copy+paste (control+C, control+V) the ToDo entity into a new one (ToDo2)
  • Delete the original ToDo entity
  • Rename ToDo2 to ToDo
  • Fix the ToDo.Id identifier, as Dorine explained (Data Type = Integer, Autonumber = Yes)
  • Publish the CS module

Note: Although this new entity has the same name as the old one, it is a different object and will be bound to a different DB table.

Finally, go to the consumer module > manage dependencies and (1) remove the "old" entity reference, and (2) add the new one.

This is an acceptable workaround for not being able to change the PK for the underlying table (no access to DB), when you don't care about the data (early development phase).

Hi Paulo and Dorine
Thanks again!!

I have done all the changes as Dorine has suggested:


  1. With the DBCleaner I could delete most of the ToDo_JCS module remnants BUT two remains and if I want to delete those the following error is given:

  2. After opening the ToDo_JCS and ToDo_Core_JCS from the oml's they are now associated with "Independent Modules". How can I "rename" them to belong to the ToDo_JCS app?

  3. When I test the app (Running from the "Independent Modules" ) by opening the To_DO_JCS (Independent Modules) in the browser I do not get the error which started this whole journey BUT if I add a new To Do it does not Update and add to the DB.

  4. On page 21 of the "List and Detail Screens Exercise" pdf it is clear that the ToDo.Id is NOT "Data Type = Integer, Autonumber = Yes" .

    Does this mean that the pdf will be changed?

Johan Strydom wrote:

Hi Paulo and Dorine
Thanks again!!

I have done all the changes as Dorine has suggested:


With the DBCleaner I could delete most of the ToDo_JCS module remnants BUT two remains I think that's fine, I was mostly concerned with deleting the old todo database table, but as Paulo explained, that's not even necessary.  @Paulo, thanks for that, I thought copy-paste would mean replacing all references to the old todo in the consumer model, but apparently, this works on name, so great tip !


After opening the ToDo_JCS and ToDo_Core_JCS from the oml's they are now associated with "Independent Modules". How can I "rename" them to belong to the ToDo_JCS app? Create application with name ToDo, but cancel cration of module in it.  Then go to independent modules and move both modules to ToDo application.
When I test the app (Running from the "Independent Modules" ) by opening the To_DO_JCS (Independent Modules) in the browser I do not get the error which started this whole journey BUT if I add a new To Do it does not Update and add to the DB.  You'll have to do some more debugging there, I think.  Did you get these 2 warnings ?  because of the earlier wrong identifier, there are 2 aggregates that need fixing by removing the join and removing the priority as source :


On page 21 of the "List and Detail Screens Exercise" pdf it is clear that the ToDo.Id is NOT "Data Type = Integer, Autonumber = Yes" . I'm looking at page 21 and don't see what you mean, can you point to where exactly you see this ? 

Does this mean that the pdf will be changed? You'd have to ask the makers of the pdf, most people on the forum are just fellow developers.


If you get stuck, see attached oap, this is after using Paulo's method for changing the identifier, I tested and can add todo's.

good luck,

Dorine