The INSERT statement conflicted with the FOREIGN KEY constraint

Hi The OutSystems Team

I'm doing the Web Development Basics course and I'm doing the exercises of the lesson 12 that has the name "Data Queries and Widgets II".

At the moment that I'm creating data through the "Add Cast/Crew to " screen by clicking the save button, the following error occurs:

The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_Y9W_PERSONMOVIEROLE_OSUSR_Y9W_MOVIE_MOVIEID".The conflict occurred in database "STDS1C021", table "dbo.OSUSR_Y9W_MOVIE", column 'ID'. The statement has been terminated.

Could anyone help me solve this problem please?

Regards

Edgard Jr

Hi Edgar.

The entity you're trying to save a record into, has a mandatory field named MovieId.

So, as this is a foreign key, the database will look the value you are providing, to see if it exists in the related Entity (Movie).

The problem is that you are NOT providing this value, so the platform assumes 0. But the IDs in Movie entity starts at 1, so the database don't find a match and raises the error.

You probably are passing to the screen where this action is being executed, a MovieId, through an Input Parameter.

Assign its value to the MovieId attribute of the entity in the Form's record you're using, before calling the CreateOrUpdate entity action when saving data.

Cheers

Hi Edgar,

This typically happens in a database when the parent table of the the governing relationship between two tables doesn't have the record in place for the child table you are attempting to insert a record 'to'. 

Best Practice
- use logic to perform a lookup on the parent key from the parent table and add it to the insert statement. 

Hope this helps.

Eduardo Jauch wrote:

Hi Eduardo,

At first I checked and understood that entities are populating correctly.
Because I still do not know the platform well and I'm a beginner, I may have made some mistake during the steps performed following the pdf file from pages 4 to 12. I had difficulty with the letters J, N, O and S. I have already checked these steps and they seem ok but there may be some mistake.
The PDF file and the "Error Foreign Key-Lesson 12.docx" file are attached, which has the steps performed until it gives the error.

Regards

Edgard

Eduardo Jauch wrote:

Eduardo, follow the other file

Hello Edgar.

As I said, the page to add cast/crew to a movie, must receive the MovieId in an input parameter.

You need to add it (using an Assignment) to the form/record that you are using to save to the db, as the record has a MovieId attribute that is mandatory. You need to do this BEFORE the CreateOrUpdate action in the Save screen action.

Cheers.

Edgard Jr wrote:

Edgard, 

Like Eduardo said, you need to add the MovieId parameter to the add cast/crew movie page. Check the point 1.e and 1.f on page 5 of the PDF you attached.

Also check the point 3.e on page 11 on same pdf.

But i would recommend to you to recheck all the steps on that pdf from start to see if you missed anything and to understand what is done in there.

Abílio Matos

Hi Gays,

The error continues. 

See my considerations in the attached file named "Error Foreign Key.docx", please!

Regards

Edgard

Hi Edgard,

See this?

In Assignments, the variable RECEIVING the value is above the '=', while the expression PROVIDING the value is AFTER the '='.

So, you must exchange the PersonMovieRoleForm.Record.MoveId with the MovieId (that is your Input parameter).

Cheers.

Hi Eduardo!

The problem was solved.

Thank you very much four your patience and cooperation.

Best regards

Edgard

Hi all,

Yesterday I ran into the same problem for the same 'Save' screen action in the same exercise. There was an error in the assignment and I corrected it as suggested above and redeployed the application. But still the same run-time problem is occurring. Is there anything else I should do? 

thanks,

Nagarajan

Funny, I did exactly the same mistake in exactly the same place.

The suggestion above resolved it.

Hi team OutSystems,

I'm also following the development course and I ran into the same problem mentioned above. There is definitely a mistake with some of the steps in the course as I followed all the steps correctly. I did add the 'MovieId' and the 'PersonMovieRoleForm.Record.MovieId in those values in the assignment like Eduardo Jauch mentioned (not mentioned in the pdf-file of the course) in the comments above, but like Nagarajan (also in the comments above) I'm running into the same problem during runtime and the following message keeps showing:

The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_Y9W_PERSONMOVIEROLE_OSUSR_Y9W_MOVIE_MOVIEID".The conflict occurred in database "STDS1C021", table "dbo.OSUSR_Y9W_MOVIE", column 'ID'. The statement has been terminated.

Hello Stefan.

You probably skipped a step.

The error says that you are trying to save a record to the PersonMovieRole, but you are not providing the MovieId value.

The exercise, item 6, is the item that tells how to create this:

If you did this, and this assign is BEFORE the CreatePersonMovieRole

And you are still experiencing the problem, the error possibly is in the previous page (the MovieDetail), as you need to pass the MovieId when navigating to this page, that is made in the 1.g (Add participants to a movie)

Check if you are not passing NullIdentifier().

I am delivering a class right now and the documentation seems in order here.

Cheers.

Eduardo Jauch wrote:

Hi Eduardo,

I probably did something wrong at the Assignment property, however, I do get a different error message now? I've checked the data of the 'PersonMovieRole' in the data overview and I see the same numbers that are mentioned in the error message 2, 5 and 3 but honestly I don't know what to with that information or where to look for? Did I do some duplicates in the widget tree?

Hi Stefan, 

I am not sure I understood. Are you referring to the error message mentioned in the exercise document?
If so, this happens when you try to assign a repeated person, with the same role to a movie where you already have they previously.

So, if you try to insert person 2, with role 3 in movie 5, because of the index unique that you set previously, the database will not allow you and will raise an exception. This is how it is supposed to work, to avoid repeated data in the entity.

Cheers.

Eduardo Jauch wrote:

Hi Eduardo,

Yes, I've figured out the problem. I added some info to the database and saved that progress. However, I thought it didn't save the new info when I clicked on the save button. So when I tried to add the same data it gave me that 'duplicate error'.

I hope you understand what I'm trying to say, but anyway thanks for your help. At least the problem is solved now.

Have a nice day

Hi, 

Yes, I understand. Once there is data there, the unique index will prevent a new record with the same data. 

Cheers