Data Modelling Stereotypes - meaning?

[Paolo F Cantoni]

When dealing with the Data Modelling Profile, the following stereotypes are defined for use in the table operations area:

check - opttable - A Check constraint to enforce domain integrity
FK - opttable - A Foreign Key
index - opttable - An index of one or more columns
PK - opttable - A Primary Key
unique - opttable - A Unique constraint to enforce the integrity of a database automatically.

It would appear that check, unique and FK define Constraints (without reference to whether or not an index is created).

It would appear that index defines the presence of an index (regardless of whether it is unique or not).

It would appear that PK defines a Primary Key Constraint and its accompanying unique index.

Can this be confirmed (or not) by a Sparxian?

I'm asking this as we suspect problems reverse engineering our Oracle 9i database, but without understanding the exact meaning of the model we are viewing it is difficult to determine where the error may be.

Thus, supposing I have a Foreign Key Constraint named FK_SOURCE_FIELD_TARGET.  I should see only an «FK» entry

If I have also defined an inversion index II_FOR_FK (which covers the columns of the FK Constraint), I should also see an «index» entry.

If the inversion index also happens to be unique (and thus, de facto, providing a Uniqueness Constraint), I should also see a «unique» entry should I not?

What combinations of entries should I see for what types of table structures?

Paolo

[hd]

It would appear that check, unique and FK define Constraints (without reference to whether or not an index is created).

Correct.

It would appear that index defines the presence of an index (regardless of whether it is unique or not).

Select the index in the Operations properties dialog and select Extended Properties... button to see it's uniqueness.

It would appear that PK defines a Primary Key Constraint and its accompanying unique index.

Primary key implies uniqueness.

As for the inversion index issue, please lodge a report with Sparx.

[Paolo F Cantoni]

Thanks for the prompt response Henk.

I knew about the Extended properties.

Are you (implicitly) saying that setting the index to unique doesn't automatically create a Unique Constraint?

Is that the Inversion Index issue you mentioned?  I wouldn't have expected EA to create the inversion index if I hadn't done so in the DB.

Paolo

[hd]

Are you (implicitly) saying that setting the index to unique doesn't automatically create a Unique Constraint?

Yes.

If I have also defined an inversion index II_FOR_FK (which covers the columns of the FK Constraint), I should also see an «index» entry.
 
If the inversion index also happens to be unique (and thus, de facto, providing a Uniqueness Constraint), I should also see a «unique» entry should I not?

How did you create the index? You need to create the index operation first, then select the column.

[Paolo F Cantoni]

How did you create the index? You need to create the index operation first, then select the column.

Sorry, not following here..

I can define the Foreign Key Constraint (on the Table, not the Relationship) and then assign the Columns.

I can separately define an Index, and then assign it the same Columns as the Foreign Key Constraint (although not necessarily in the same order).  This is the Inversion Index (because it has the same Columns as the Foreign Key Constraint) and can be used for reverse (inversion) look-ups by the DBMS.

If the relationship between the tables happens to be one-to-one then I can set the inversion index to Unique.  This, in turn, should generate a Uniqueness Constraint on the columns of  the index.

Now, the reverse isn't necessarily true.  If I have a unique index and Unique Constraint and change the index to non-unique (or even delete it) the Unique constraint may still apply (since a unique index isn't required to check for uniqueness).

Does that help?

To my mind, the best UI is:

If I set an Index to unique, EA should check if a Uniqueness Constraint exists for the column set of the Index.  If it doesn't, pop up a dialog saying EA will create one (or Cancel to revert the uniqueness setting).  If OK, the index's stereotype should change to «AK» (Alternate Key) - since that's what it now is.

In the reverse direction,  if I remove the unique setting on the index, then EA should ask if I want to also remove the Uniqueness Constraint (since there must now be one).  The stereo type changes from «AK» to «index».

Thoughts? Votes?
Paolo

[size=0]©2006 Paolo Cantoni, -Semantica-[/size]