Foreign Key constraint error when inserting a record

Foreign Key constraint error when inserting a record

  

Hello all,

I'm having an issue when it comes to inserting a record into a table that has a foreign key link.


This is the error I'm getting: The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_EX8_MATERIALTESTREPORTDETAIL_OSUSR_EX8_MATERIALTESTREPORT_MATERIALTESTREPORTID". The conflict occurred in database "OSDEV1", table "dbo.OSUSR_EX8_MATERIALTESTREPORT", column 'ID'. The statement has been terminated.


Below is a data model of the data I'm working with. The goal of this web app is to take records from an old ERP database and convert them into this one that has a different structure and different column names. 


The insert into the "MaterialTestReport" table works flawlessly, it's the detail table insert that doesn't work.

Any help is appreciated, I can't figure out why it's tripping up on the foreign key. Everything on my end (like the actual data) seems to be correct and should work together with no problems.


Thanks!

Hi Braeden,

You seem to be forgetting to assign the MaterialTestReportId foreign key of the record you're passing to CreateOrUpdateMaterialTestReportDetail with the Id of MaterialTestReport you previously inserted (it will be the output of the CreateOrUpdateMaterialTestReport you mention is working flawlessly)

Hi,

Yes would need to pass the same id for inserting in child table.


Thanks!

Braeden,

After re-reading my answer, I realize my explanation might be a bit confusing, will try making it clearer, with a step-by-step description:

  1. Insert a new MaterialTestReport record using the CreateOrUpdateMaterialTestReport Entity Action;
  2. For each of the related MaterialTestReportDetail records you want to insert (I'll assume the entity record variable you are using is called Detail), don't forget to:
    1.  Assign to Detail.MaterialTestReportId the value of CreateOrUpdateMaterialTestReport.Id
    2. Pass the Detail as input parameter to the CreateOrUpdateMaterialTestReportDetail Entity Action in order to insert a new MaterialTestReportDetail record;

Be careful that the best practice for OutSystems entities is to have the Ids handled by the platform itself (by default, when you create an entity, the Id will be an auto-numbered Long Integer), which means that primary keys in the OldMaterialTestReport and OldMaterialTestReportDetail external entities would be different from the Ids in the MaterialTestReport and MaterialTestReportDetail entities.

Jorge Martins wrote:

Braeden,

After re-reading my answer, I realize my explanation might be a bit confusing, will try making it clearer, with a step-by-step description:

  1. Insert a new MaterialTestReport record using the CreateOrUpdateMaterialTestReport Entity Action;
  2. For each of the related MaterialTestReportDetail records you want to insert (I'll assume the entity record variable you are using is called Detail), don't forget to:
    1.  Assign to Detail.MaterialTestReportId the value of CreateOrUpdateMaterialTestReport.Id
    2. Pass the Detail as input parameter to the CreateOrUpdateMaterialTestReportDetail Entity Action in order to insert a new MaterialTestReportDetail record;

Be careful that the best practice for OutSystems entities is to have the Ids handled by the platform itself (by default, when you create an entity, the Id will be an auto-numbered Long Integer), which means that primary keys in the OldMaterialTestReport and OldMaterialTestReportDetail external entities would be different from the Ids in the MaterialTestReport and MaterialTestReportDetail entities.

Hey Jorge,

Thanks for the reply! Sadly that is what it's been doing from the start, below is a picture of the action I've created to import these records (right now it's just set up to import one).

The assign statement right before the highlighted action just assigns the ID of the newly created Material Test Report to a variable called "MTRid". IT is then passed into this "TransferMTRDetail" action which assigns some more variables and then uses the "CreateOrUpdateMaterialTestReportDetail" action.

Below is a picture of the debugger showing that all the variables that should be populated have been populated (my test record was ID#5070). The "Charpy" column is not required so I don't think that could be causing the issue.

 

Sorry if my initial post was too vague!

Hi Braeden,

That looks fine, but as you said, the actual create/update is inside TransferMTRDetail.

You might doing something there which might accidently erase the FK..

(It would help if you provide the oml btw)



Hello Braeden,

It seems to me that the problem is not the generation of the NEW ID, but your logic inside the TransferMTRDetail.
Probably a debug in that server action will point you the error, but if you need you can share here the code and we can help you.

In any case, the error is that you are trying to insert a new record in the MaterialTestReportDetail entity, and you are not assign a valid value for the MaterialTestReportId fk.

You can have a wrong assignment (inverted origin and value), you may have the assign after the create action, you may be overwriting the value somehow, etc.

But the error is inside the TransferMTRDetail.

Cheers.

EDIT: Sorry J. I was typing and doing something else and so my answer got after yours.

Eduardo Jauch wrote:

Hello Braeden,

It seems to me that the problem is not the generation of the NEW ID, but your logic inside the TransferMTRDetail.
Probably a debug in that server action will point you the error, but if you need you can share here the code and we can help you.

In any case, the error is that you are trying to insert a new record in the MaterialTestReportDetail entity, and you are not assign a valid value for the MaterialTestReportId fk.

You can have a wrong assignment (inverted origin and value), you may have the assign after the create action, you may be overwriting the value somehow, etc.

But the error is inside the TransferMTRDetail.

Cheers.

EDIT: Sorry J. I was typing and doing something else and so my answer got after yours.

Hey Eduardo,


Here's what I'm working with in the TransferMTRDetail action:


The debugger in my last reply is actually from this action! It's what I get when the debugger hits the CreateOrUpdateDetail action. I'll post it again below!


Thanks again for all the help guys! I really appreciate it.

J. wrote:

Hi Braeden,

That looks fine, but as you said, the actual create/update is inside TransferMTRDetail.

You might doing something there which might accidently erase the FK..

(It would help if you provide the oml btw)



Hey J,

I've posted a bit more detail in my reply to Eduardo.


I would post the .oml file but amount of data in this web app is insane. I took the data from my company's old ERP system which for some reason stored PDF files as binary and there are roughly 6000 records, so the file would be pretty big.


You can always copy the relevant code to a blank.oml

Because it's now simply guessing still by the stuff you provide.

which may or may not be 100% what is done.

for example, what do you provide as input to the createOrUpdate

and this, do you assign it correctly (you did not accidently swap the detailid/id) in the first 2 assigns





Just to be sure, but what are you passing as input to the CreateOrUpdate action that is triggering the error?

It is because I look to your data (for what is possible, as it is cut), and I am with a big impression that you are not assigning the ID of the "master" record to the FK in the record you are trying to save (well, I'm pretty sure this, or it is getting overwritten).

J. wrote:

You can always copy the relevant code to a blank.oml

Because it's now simply guessing still by the stuff you provide.

which may or may not be 100% what is done.

for example, what do you provide as input to the createOrUpdate

and this, do you assign it correctly (you did not accidently swap the detailid/id) in the first 2 assigns





Hey J,


I've attached an .oml for you to take a look at


Eduardo Jauch wrote:

Just to be sure, but what are you passing as input to the CreateOrUpdate action that is triggering the error?

It is because I look to your data (for what is possible, as it is cut), and I am with a big impression that you are not assigning the ID of the "master" record to the FK in the record you are trying to save (well, I'm pretty sure this, or it is getting overwritten).

Hey Eduardo,

I've attached an .oml file to J, hopefully that might give you guys a bit more insight.

Hopefully it has everything you guys need.


Thanks guys! 


Braeden Stewart wrote:

I've attached an .oml for you to take a look at

Thanks..

However, what is being doen inside the CreateOrUpdateDetail, it's a wrapper, so perhaps its going wrong there?

On a different note, you should work on the names, because it's pretty hard to read, and for example ID is so ambigious, it's not easy to tell if that's the right id or not.

(also the usage of so many local variables is a bit overprocessing as far as i can see, but first fix the issue :D )

J. wrote:

Braeden Stewart wrote:

I've attached an .oml for you to take a look at

Thanks..

However, what is being doen inside the CreateOrUpdateDetail, it's a wrapper, so perhaps its going wrong there?

On a different note, you should work on the names, because it's pretty hard to read, and for example ID is so ambigious, it's not easy to tell if that's the right id or not.




CreateOrUpdateDetail is literally just the automatically created action with the same name.

My boss wanted us to expose read only on the tables so this is the work around so we can still use those functions. 

Sorry that the names are confusing, I'll work on changing them up to be more descriptive. I'm not really a developer or anything so I know I've got stuff to work on learning haha

er, yes, but does the CreateOrUpdateXXX use the input as:

Source

or 

Source.Record.


Just a question...

Are you completely sure that the entity the first CreateOrUpdate (MaterialTestReport) is inserting a record is the same one your FK is pointing to?

By your code it seems that they are DIFFERENT tables (one defined in the DATA module, the other in this module...)

If so, in fact, when you try to create a NEW DETAIL, the ID you have does not exists in the table the DETAIL you are creating points to...

J. wrote:

er, yes, but does the CreateOrUpdateXXX use the input as:

Source

or 

Source.Record.


It uses "Source"

what Eduardo is saying..


I am getting confused by the aggregate 

GetDetailByMasterID

How is it possible to fetch some record from an OldTable with an ID in the new table?



J. wrote:

what Eduardo is saying..


I am getting confused by the aggregate 

GetDetailByMasterID

How is it possible to fetch some record from an OldTable with an ID in the new table?



The ID in the new table (the master) should be the same as what was imported from the old table, using that ID it filters the MaterialTestReportID (The foreign key) from the OldMaterialTestReportDetail table by the ID. 

It's just essentially getting all the detail records that correspond to the master record.


Solution

Braeden Stewart wrote:

J. wrote:

what Eduardo is saying..


I am getting confused by the aggregate 

GetDetailByMasterID

How is it possible to fetch some record from an OldTable with an ID in the new table?



The ID in the new table (the master) should be the same as what was imported from the old table, using that ID it filters the MaterialTestReportID (The foreign key) from the OldMaterialTestReportDetail table by the ID. 

It's just essentially getting all the detail records that correspond to the master record.


Well, so, probably, this is the cause of the error.

Your new entities have id's defined as auto-number.

Any value you pass as id when creating a new record will be ignored if they are not found in the entity (In a CreateOrUpdate), and a new id will be given.

If you are relying in the old Id in the FK it will not exist in the new entity (most likely)...


Solution

Eduardo Jauch wrote:

Braeden Stewart wrote:

J. wrote:

what Eduardo is saying..


I am getting confused by the aggregate 

GetDetailByMasterID

How is it possible to fetch some record from an OldTable with an ID in the new table?



The ID in the new table (the master) should be the same as what was imported from the old table, using that ID it filters the MaterialTestReportID (The foreign key) from the OldMaterialTestReportDetail table by the ID. 

It's just essentially getting all the detail records that correspond to the master record.


Well, so, probably, this is the cause of the error.

Your new entities have id's defined as auto-number.

Any value you pass as id when creating a new record will be ignored if they are not found in the entity (In a CreateOrUpdate), and a new id will be given.

If you are relying in the old Id in the FK it will not exist in the new entity (most likely)...


This definitely is the problem! I forgot that the ID column would auto number itself, I passed in an ID thinking the ID would 5070. 

Gotta love silly little oversights.


Thanks a lot guys!