Bug: DB Relationship Roles

[Paolo F Cantoni]

Database relationships are represented in EA by Associations.

The destination (target) role should be the key of the destination table that is being used for foreign key mapping.  The origin (source) role should be the columnSet that makes up the corresponding values for the selected destination table key.

NOTE: under the relational model, the destination key merely has to be unique.  It does not need to be the primary key.  NOTE: IDEF1X standard does enforce primary key only, but that's the US Department of Defence's problem, not ours.

The destination table keys are defined by stereotyped («PK», «unique») operations.  These must be defined before they can be used as part of the relationship definition.

The origin columnSet is commonly known as the Foreign Key, but it doesn't need to have any indexing or keying applied - it's just a set of columns in the same order as the key you are mapping to in the destination table.  Under the UML profile that EA uses for data modelling, the columnSet is represented as a stereotyped  («FK») operation.  Because of this, in order to be considered for the origin (source) role, the «FK» operation needs to be defined before use.

Accordingly, the destination (target) role drop-down MUST show only those indexes on the destination table which are unique.

Similarly, the origin (source) role drop-down MUST show only those («FK») operations - preferably ONLY those whose columnSet match the columnSet of the selected destination table key [Edit: and which are not already in use].

This is not currently the case and can cause serious corruption as a consequence.

In my view, a «index» stereotyped operation with the attribute Unique should be re-stereotyped as «AK» - Alternate Key since, under the Relational Model,  only Primary and Alternate Keys can be considered targets for database relationships.

Thoughts?
Paolo

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

[Paolo F Cantoni]

NOTE: the above posting, only really applies where a declarative relationship (via a Foreign Key Constraint) exists or is being modelled.

Often however, especially when called in to refactor/redesign an existing database, it is  necessary to document relationships that are inferred by, or derived from, the extant data or, more typically, triggers.

In this case, in my view, it is too dangerous to enforce the Foreign Key Constraint.  Indeed, it would be useful to have the flexibility to use any column (at the appropriate end) as the role for the inferred relationship.

As refactoring proceeds, one could define appropriate columnSets (no implication of indexing) and then the Foreign Key Constraint would be defined and the Foreign table mapping created...

Thoughts? Votes?
Paolo

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

[barry_abs]

Hi Paolo - i'm not sure if my issue is related to yours, precisely but the main problem i have with FKs is as follows:

* On a table, you cannot have two or more FK constraints referencing the same target key

I Have a UNIT table:

CREATE TABLE Unit (
UnitID NUMBER(12) NOT NULL,
Name VARCHAR2(32)
)

and i have a UNITCONVERSION table:

CREATE TABLE UnitConversion (
FromUnitID NUMBER(12) NOT NULL,
ToUnitID NUMBER(12),
Gradient NUMBER,
Offset NUMBER
)

I want to create TWO FK constraints from UNITCONVERSION to UNITS:

1. FromUnitID -> UnitID
2. ToUnitID -> UnitID

This is not possible in EA at this time.. It won't let you add the second FK, it updates the first changing the source key to a composite of FromUnitID and ToUnitID - unwanted.

L.

[Paolo F Cantoni]

Hi Paolo - I'm not sure if my issue is related to yours, precisely but the main problem i have with FKs is as follows:

* On a table, you cannot have two or more FK constraints referencing the same target key
[size=13][SNIP][/size]
I want to create TWO FK constraints from UNITCONVERSION to UNITS:

1. FromUnitID -> UnitID
2. ToUnitID -> UnitID

This is not possible in EA at this time.. It won't let you add the second FK, it updates the first changing the source key to a composite of FromUnitID and ToUnitID - unwanted.

L.

In order to have two FK Constraints, you need to have two Relationships (Associations).  Each one has the appropriate FK constraint.

Do you have the two relationships defined?

HTH,
Paolo

[hd]

Because of this, in order to be considered for the origin (source) role, the «FK» operation needs to be defined before use.

Use the Foreign Keys dialog (association context menu) to create the foreign key relationship. The FK operation is then created at the same time as the relationship. Only the allowable columns are listed.

[Paolo F Cantoni]

Use the Foreign Keys dialog (association context menu) to create the foreign key relationship. The FK operation is then created at the same time as the relationship. Only the allowable columns are listed.

Hi Henk,

This is exactly my point! I'm not saying it's wrong, I'm just making the observation...

The posting was about using the roles drop-down...  The ONLY items in the list should be those defined as FK operations.  If the one you want isn't there, you need to invoke the FK dialog to create it.  Now, a button next to the role drop-down to invoke the dialog [[New FK...]] would be nice.

Paolo

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