How is the referential integrity maintained in JDE tables ?. In the table design aid, I see an option to include only Primary keys and not foreign keys. Any specific reason on why there isn't an option to mark a field as foreign key?
The short answer is: it isn't. JDE does not use foreign keys in the database layer. All referential integrity is maintained in the software layer and thus we have a whole stack of integrity reports that have to be run to deal with orphaned records.
There's probably lots of reasons. The main one being that JDE started life on a system/38 so true RDBMS wasn't available when it was being designed and built. Why do you think we still have 30 character description fields and dates stored as numbers and numbers stored as strings? Those are all a legacy of having software designed and built in the early 80s.
Exactly. And this is all handled in OMW. You create your tables, index, etc. and generate them in the DB using TDA and OMW. If Oracle had to add in a graphical design tool to define and manage relationships, cascading updates/deletes, etc. - the complexity would go up exponentially. Even to define all the relationships in an excellent tool like MS SQL Management Studio which can graphically go from diagram to constraint and constraint to diagram would be a monumental undertaking. You obviously couldn't have all 5000+ tables in one DB diagram, you would have to break it up.