Hi,
I am using JDeveloper 10.1.3.4 and have a question as indicated by the topic of this posting. By a design flaw, I added foreign key constraint on a table at the database level, for no other purpose than achieving a master/detail relationship between two VOs. Now the foreign key constraint has been dropped to remedy the flaw, But I am not sure if the application will cause problem down the road.
The application in question has a table named BILL and another table named VALIDATIONS, both having the same composite primary key (LDAP_UID, TERM), where TERM means semester.
BILL is about students, the balance they owe, etc. VALIDATION saves the commitment students make:
BILL
------------------------------------------------
LDAP_UID TERM BALANCE
------------- -------- --------
john.miller 200902 1500
VALIDATIONS
------------------------------------------------
LDAP_UID TERM CONFIRM_NUMBER
------------- -------- --------------
john.miller 200902 0901100005
The foreign key constraint was added on the VALIDATIONS table, for VALIDATIONS(LDAP_UID, TERM) to reference BILL(LDAP_UID, TERM). When the data model is built up in the application, an entity association was automatically created from this foreign key constraint, and then a view link was created based on the entity association to achieve mater/detail effect in the application:
Table: BILL
Table: VALIDATIONS
Foreign Key: VALIDATIONS(LDAP_UID, TERM) references BILL(LDAP_UID, TERM)
Entity object: Bill
Entity object: Vallidations
Entity Association: ValidationsBillFkAssoc (based on the foreign key)
View object: LoggedInStudent (from Bill)
View object: OneValidation (from Validations)
View link: ValidationByOneStudent (based on ValidationsBillFkAssoc. This achieves master/detail
between LoggedInStudent and OneValidation)
This foreign key is now found to cause problem and must be dropped: The BILL table holds at most three semesters' data, whereas the student commitments cumulatively saved in the VALIDATIONS table must be kept permanently. When the data of older semesters are offloaded from the BILL table, the rows saved in VALIDATIONS table become orphaned.
I believed that the view link and the entity association were dependent on the foreign key, therefore I removed them before dropping the foreign key in the order listed here:
Removed usage of of the VO (OneValidation) from the application module
Deleted the view link (ValidationByOneStudent)
Deleted the entity association (ValidationsBillFkAssoc)
Dropped the foreign key in the database
The master/detail relationship between the two VOs is still needed. I added back the view link, which now is based on the common attributes (LdapUid, Term) between the two VOs, rather than based on entity association. The changed application works fine with the changed database, and now the rows in the VALIDATIONS table will not be orphaned.
What's interesting and also disturbing is that when I run a copy of the application that was saved prior to these changes, against the database that has dropped the foreign key constraint, the application still works, with the master/detail and all. And the dependencies do not seem to matter. But do they?
My question is: does an entity association require a corresponding foreign key in the database? if I use the copy of the application prior to the changes, will it be OK or will it cause unforeseen problems down the road?
Thanks!
Newman
Edited by: J. Newman on Oct 6, 2009 10:31 AM