BAD: [ ] Unique - totally defective

[Paolo F Cantoni]

The 7.0 Build 818 release notes contain the line:

Corrected behavior of table column PK, Unique and Not Null options.

I don't know whom Sparx are using as an authority on the correct behaviour of the Uniqueness options within tables, but the current ( build 818 ) behaviour is TOTALLY contrary to Data Modelling Theory and the Relational Data Model.

I have posted numerous times what the correct behaviour is:
The [X] PK checkbox ONLY means that the column is part of the foreign key.  More than one column may be so designated. {this part works}
The [X]Unique checkbox means that the column itself is unique (and therefore a single column uniqueness constraint needs to be generated).  If a column appears in a compound (multi-column) uniqueness constraint, NO inference may be drawn as to whether any individual column in the uniqueness constraint is unique.  In particular, unmarking the unique checkbox DOES NOT mean you should remove the column from any compound uniqueness constraint.  (It can, however, be removed from any single column uniqueness constraints, thereby removing the constraint method)  {this part is totally defective}
Where the Primary key is a compound (multi-column) key, NO inference may be drawn as to whether any individual column in the key is, itself, unique.  {this part works}

This defect is SO BAD (Broken as Designed), that an immediate hotfix MUST be issued with the correct behaviour!  The consequent unwitting corruption of the data models is too severe!

Paolo

[size=10]Using EA in spite of EA, NOT because of it![/size] ^TM[/color]

[jeshaw2]

Paolo;

If I understand things correctly (according to relational theory)

• Multiple columns may be used to form a key
• A compound key, as a holonym, may have an isUnique constraint specified, but
• no individual column within a compound key may be specified isUnique
  

The reasoning for this is that if a column in a compound key is unique, the other columns in the key would be moot.  The isUnique column would be sufficient to identify the row.

Do I have it right?  If I am, I vote for the EA logic to enforce this rule.

-Jim

[Paolo F Cantoni]

Paolo;

If I understand things correctly (according to relational theory)

• Multiple columns may be used to form a key
• A compound key, as a holonym, may have an isUnique constraint specified, but
• no individual column within a compound key may be specified isUnique
  

The reasoning for this is that if a column in a compound key is unique, the other columns in the key would be moot.  The isUnique column would be sufficient to identify the row.

Do I have it right?  If I am, I vote for the EA logic to enforce this rule.

-Jim

Not quite - you need to distinguish between a true key and a unique index...

You statement is true for true keys (The key, the whole key, and nothing but the key - is the adage/mantra).  

However for various operational purposes you may use a(n) (already) unique column in an index to create a unique index.  But, as above, you're not creating a key - you're creating a unique index.  A subtle, but very important, distinction which most of the literature and courses DON'T make (as I've noted elsewhere within).

EA doesn't, yet, make such distinctions but the current implementation ( 818 ) is seriously broken!  If you create ANY unique constraint involving the column, the unique checkbox is marked true - which is wrong (or at least indeterminate) if the unique constraint involves more than one column (is compound).

HTH,
Paolo

[«Midnight»]

Paolo's on the right track here.

There was a similar - similar looking, though possibly unrelated - bug in the EA 7.0 betas. Though the bug (as I reported it) was resolved, the solution - to the bug and possibly to the underlying flaw as well - seems to leave much to be desired.

I've been trying to get a grip on some of the key and index behavior in EA 7.0 for a while. Unfortunately the data model I was working on was relatively simple, and worked out well. [Success can really interfere with further investigation.]

Paolo's really been getting into the gist of this stuff. I suspect (without proof, but this is an educated guess) that several of the problems he's reported recently are related to a small area of the engine. This is the time to get it repaired.

Just my 0.02 CAD, since the CAD lately seems to be worth something.

David

[sargasso]

However for various operational purposes you may use a(n) (already) unique column in an index to create a unique index.  But, as above, you're not creating a key - your creating a unique index.

Absolutely 100% correct.  To put it another way.. Only one key is important. However, many indices can be extremely important and  their structure and constraints are a matter for the designer.  To expand Paolo's point, this is extremely important where query optimisers are involed in relaxed (partial key) solutions for stored queries ("fuzzy" views).

bruce



 

[lhyabcd]

Hoping it be fixed ASAP.

[«Midnight»]

Yes, and I wrote to Sparx about that during the 7.0 beta. [Though I mentioned different behavior between 6.5 and 7.0, both incorrect.]

At the time I was working on a client site, and cannot verify that the report went through. Some partial corrections were done, but there were other things going with database settings at the same time.

David