Hi, We have this requirement (simplified version): There are 2 tables

senthilar over 10 years ago

Hi,
We have this requirement (simplified version):
There are 2 tables - Employee, EmployeeTasks. The EmployeeTasks table has a reference to Employee table (employeeId column) and it can have several rows for the same employee. There are columns in EmployeeTasks table which are NON-NULLABLE. Our process produces employee object first and the employee task object would be produced after several hours (or maybe days).
What we need is, we should insert into the Employee table as soon as we have employee object without waiting for employee task object(s). The EmployeeTask can be inserted as and when we get the object populated by our process.

To bring the proper Foreign key constraint between the 2 tables, we added the employeeTask CDT inside the employee CDT. This way the tables get created properly with correct constraints. But, when we try to insert employee (by using Write To DataStore smart service and mapping employeeCDT), we get "cannot insert nu...

Discussion posts and replies are publicly visible

0 senthilar over 10 years ago

...ll into xyz (non-nullable) column in EmployeeTask" error.

So, we deleted CDTs and recreated this way - EmployeeCDT doesn't have reference to EmployeeTaskCDT, but EmployeeTaskCDT contains a field called employeeId which is of type EmployeeCDT. This way, again, the DB constrains are established correctly, and we are able to insert into Employee table without facing problem. But when we insert into EmployeeTask we face problem. If we just populate the employeeId in the EmployeeCDT within the EmployeeTaskCDT, all data is getting wiped off in the row in Employee table. Of course we could populate all the fields in EmployeeCDT within the EmployeeTaskCDT, but then everytime we insert into EmployeeTask we would unnecessarily be updating Employee.
This left us wondering what would be the right way to solve the issue and also meet our requirement. Pls let know your thoughts.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 venkats533 over 10 years ago

did you check best practices for one-to-many relationship and how to define in XSD ? Your use case a typical one-to-many relationship, i.e. every employee has zero or more tasks. Please do not try to put employee under employee task, that is logically reverse in order.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 10 years ago

Couple of things... firstly, make sure you set minOccurs=0 if that is what you want.
Secondly, check what is your CASCADE policy. This can be set via JPA annotation in your XSD.
Hope this helps.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 senthilar over 10 years ago

Thank you Sathya and Venkat! Your inputs helped resolve the issue.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel