The reason of why to avoid using DBSequence as the foreign key in the Master-details relationship when you need to post the data into the database.
A user case for this scenario: you have two tables: User and UserGrant tables. User table has columns related to user info: user_id, user_name, user_password, user_address, etc. UserGrant has columns related to user and group info: grant_id, role_id, user_id, etc. User is the master table and has the primary key user_Id as a DBSequence. The user_id is also a foreign key in UserGrant table. You create VO based on the entities that based on the two tables, and drag the UserVO to the ADF page to create a new row. When a new role (which means a new user) created in UserEO, you want a new role (which means a new grant: a new user with a role) to be created the same time in UserGrantEO. The issue rises because the posting order of the two entity objects. This is because when a new role is created (before posting data and commit), the DBSequence will be assigned to a negative number (-1, for instance). This negative number will not be replaced by the real sequence number until you implicitly call the commit (actually is the postChange method behind it). We have no idea that which entity object will be posted first. If the detail EO (the UserGrantEO in our example) try to post data first, then a negative number tried to be assigned to user_id. Two issues: 1)it's a negative number, but most importantly 2).this number doesn't exist in master EO yet.
JDeveloper 11g Handbook has pointed out this on Page 221: the importance of posting order. The workaround has been provided on this matter too.
But easily workaround way for me is to link the two tables using another column, in our case, it would be user_name, which is not a sequence. So both User and UserGrant table have the user_name column and it servers as the link for the master-details relationship.
A user case for this scenario: you have two tables: User and UserGrant tables. User table has columns related to user info: user_id, user_name, user_password, user_address, etc. UserGrant has columns related to user and group info: grant_id, role_id, user_id, etc. User is the master table and has the primary key user_Id as a DBSequence. The user_id is also a foreign key in UserGrant table. You create VO based on the entities that based on the two tables, and drag the UserVO to the ADF page to create a new row. When a new role (which means a new user) created in UserEO, you want a new role (which means a new grant: a new user with a role) to be created the same time in UserGrantEO. The issue rises because the posting order of the two entity objects. This is because when a new role is created (before posting data and commit), the DBSequence will be assigned to a negative number (-1, for instance). This negative number will not be replaced by the real sequence number until you implicitly call the commit (actually is the postChange method behind it). We have no idea that which entity object will be posted first. If the detail EO (the UserGrantEO in our example) try to post data first, then a negative number tried to be assigned to user_id. Two issues: 1)it's a negative number, but most importantly 2).this number doesn't exist in master EO yet.
JDeveloper 11g Handbook has pointed out this on Page 221: the importance of posting order. The workaround has been provided on this matter too.
But easily workaround way for me is to link the two tables using another column, in our case, it would be user_name, which is not a sequence. So both User and UserGrant table have the user_name column and it servers as the link for the master-details relationship.
Comments