Question Unique Index Not Mandatory In Progress

[philippoommen1]

Hi,

Recently I showed an ER Diagram to the customer. For six tables he found that there is no unique index defined (I knew this already). He made a statement that he is from Oracle back ground and Oracle never allows you to create a table with out a unique index.

My doubt is that why progress is allowed to do so ? Next time if some one ask such a question what answer I can give them to counter it?

Regards
-Philip-

 

[joey.jeremiah]

Without getting to the religious debate of progress vs oracle... progress would compare better to access

If an index is non-unique, progress will add a phantom rowid field to the index so all the indexes are actually unique

I agree that a table should have at least a primary key unique index (but there could be exceptions). It's most likely a case of poor design.

 

[Saptarshi Sengupta]

I had this question forever. Thank you for bringing this up Philip.

My understanding is that a Unique Index field should be something like EmployeeID , SSN , etc. Theres no need really to add a Unique index for each table. More so, because when we have a Unique index field in a query, Progress does not select Multiple index brackets, it works with the Unique one, reducing the query efficiency.
Is my understanding correct?

 

[TheMadDBA]

Apparently your customer doesn't really know that much about Oracle... Oracle does not force a unique index on a table, or any indexes for that matter.

It is considered a best practice to have a unique key on every table (regardless of DB) so that you can uniquely identify a record without resorting to ROWIDs or other internal pointers. Especially since those can and will change over time when maintenance happens or records are deleted and created.

As far as the index internals go... the rowid (or equivalent) is stored as part of the index structure, but that doesn't enforce uniqueness from an application standpoint. It is just used as a pointer to pull individual records from the database.

 

[tamhas]

Regardless of what the DB forces, why would you create a design that did not have a unique identifying index? Moreover, the index should be on a field that has no application meaning like a GUID or sequence. That field is used for joins, but has no meaning the application itself. That way, if a mistake is made in something like an SSN, you just fix the mistake and all the join pointers are just fine.

 

[TheMadDBA]

All of that is true... I was just pointing out that it is a design (or lack of) concept and not a hard constraint enforced by the database.

Without starting another tangent, there are good reasons to avoid pure 3NF in reporting and query heavy databases. Especially non Progress databases that actually have cost based optimizers that can alter the execution plan for maximum performance.