Foreign key error when inserting + updating tables

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Jon, Steve, Ian, Dave

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Foreign key error when inserting + updating tables

#1

Postby ericb » Wed Jul 18, 2018 3:57 pm

Consider the following use case: I'm making a form for administrators to be able to manage a list of subdepartment used in our application. The form allows them to create, edit, and delete subdepartment. When creating/editing a subdepartment, they can also, in the same form, manage the list of supervisors for that subdepartment. In the form this is represented as a table to which they can add/remove rows, and then select the employee they wish to be a supervisor from a dropdown list.

When saving the subdepartment, the following code is executed. I first check to see if the subdepartment ID is set; if it is, this means I'm editing an existing subdepartment rather than creating a new one. In this case I'll just update the existing subdepartment resource, then move on to updating the supervisors table, which also uses the subdepartment ID to link the employees to it. There are no issues here because the subdepartment already exists in the database.

If the subdepartment ID is not set, then I first retrieve the next value for its primary key through the Oracle sequence we're using. I can then insert the new resource row, and again move on to updating the supervisor table.

Code: Select all

if (fields.SUBDEPARTID.value != null) {
	resources.subdepartment.update();
} else {
	resources.subdepartment_SEQ.fetch();	// get the next sub-department ID from the Oracle sequence
	
	resources.subdepartment.insert();
}

tables.subdepartment_supervisors.updateTable();
The issue I'm having is when inserting a new subdepartment along with a new list of supervisors for this subdepartment. Because everything is done as a single transaction, when the table attempts to insert the new supervisor rows, I get a foreign key error because the parent key isn't found; that is to say the subdepartment row in the subdepartments table. Presumably this is because the insert to the subdepartments table hasn't been commited yet.

I encountered this same issue in other forms, and to get a around the problem I would use DatabaseServices to insert the new subdepartment, execute that statement, then be able to use .updateTable() on whatever tables needed it. This works but it's not exactly elegant since I end up needing to maintain SQL outside of a resource and set all the parameters for the insert, when the resource would normally take care of that for me.

Is there a better solution to this I haven't thought of?
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: Foreign key error when inserting + updating tables

#2

Postby Jon » Thu Jul 19, 2018 10:52 am

Because everything is done as a single transaction, when the table attempts to insert the new supervisor rows, I get a foreign key error because the parent key isn't found; that is to say the subdepartment row in the subdepartments table. Presumably this is because the insert to the subdepartments table hasn't been commited yet.
This doesn't sound right to me. You shouldn't need to commit the foreign key table in order to avoid a foreign key error - it's all inside a single transaction and Oracle is aware of this. But it might be interesting to add a commit after the insert just to see if it makes the problem go away i.e. add..

system.transactionManager.commitAndRestartTransaction();
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: Foreign key error when inserting + updating tables

#3

Postby Jon » Thu Jul 19, 2018 10:58 am

You might also take a look at this - deferrable constraints?
https://stackoverflow.com/questions/310 ... ithout-dis
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: Foreign key error when inserting + updating tables

#4

Postby ericb » Thu Jul 19, 2018 1:42 pm

Hi Jon,

I first tried Deferrable Constraints. This gave a new error, that in hindsight may have been related to what I'll explain below.

I then tried commitAndRestartTransaction() which still gave me a FK error, which greatly confused me. I decided to print the primary key throughout the script to make sure it wasn't getting reset to null at some point. I eventually realized that the PK I had in my script from the Oracle sequence (552 let's say) was not the PK that was being inserted into the DB. The PK in the DB would be 1 greater than the PK I had in my script. I then realized that the combination of two things was causing my issue all along:

1. The table has a trigger to automatically set the PK from the Oracle sequence on inserts if it's null. This shouldn't have been an issue though because it would only get the next Sequence value if the PK being inserted was null. This shouldn't have been the case since I was manually getting the next sequence value to insert in my script, except that:

2. The PK column in my subdepartment resource was set to read only and Persistent, which meant Ebase was treating it as an auto-increment value and not including it in the insert.

I just had to remove the Read only attribute from the PK in my resource, and now the whole script runs without any FK errors. Sorry about that. Hopefully if someone else makes the same mistake as me in the future, they'll stumble upon this post.
0 x


Who is online

Users browsing this forum: No registered users and 9 guests