Menu
Why doesn't Oracle have foreign keys in Oracle Apps/ E-Business Suite?
I observed that Oracle doesn't have/use referential constraints on the Oracle Apps 11i Schema. All the logic is contained in PL/SQL packages.
This seems odd coming from a RDBMS major.
861
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
E-Business Suite contains application modules that support different aspects of CRM, ERP and SCM operations, including customer service management, financial management, human capital management, project portfolio management, procurement, and value chain planning and execution.
In Oracle Designer, when defining relationships between entities and execute "Design Transformer" to generate tables, Designer generates automatically indexes for foreign keys in the Server Model.
After naming your constraint, add the words FOREIGN KEY to specify that it is a foreign key constraint. Then, open brackets and add in the name of the column in this table that will be the foreign key. Then, close the brackets. Next, add the word REFERENCES , then the name of the other table you're referring to.
I believe the main reason for not using strong referential constraints is that this suite has to deal with legacy data during setup. Most of the time you will import historical data in the ERP and this data may be incomplete.
I don't think you should view the e-business design as a reference model since it is voluntarily left incomplete (so that you can custom it to your needs).
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
