> One heads-up and data point on views: You're not going to get PK info from a > view, at least not in Oracle up to and including 11g.
Yes, that's really a pity. Oracle has all the information, internally, and makes good use of it in the CBO. It would be really great if Oracle could disclose some of this referential information in the dictionary views. On the other hand, depending on how you join tables within a view, the semantics of primary keys / foreign keys change, and it would probably be hard for the Oracle database to model this generically. > Whether that's a minor annoyance or a dealbreaker pretty much depends on your > workstyle and the overall situation. > Here, we're currently creating and modifying tables and views at the drop of > a hat, and having to manually provide PK info for views turned out to be a > hassle. And it was important to do so because the Java code uses PK fields to > determine whether two Pojos refer to the same db record. I agree it can be a hassle. You could implement a strict naming convention for primary key columns and automatically generate "view primary keys" where applicable. But as I said, as soon as you join several tables, these primary keys will have a new semantics...
