More thoughts aloud.
> Of course, that would mean we cannot easily change the "fromDate" field in
> PartyContactMech (unless we do EECA).
Correction. With foreign key constraint in place, it is not possible to use EECA to do a pseudo
"ON UPDATE CASCADE". I can't find any "turn off foreign key checks" switches in OFBiz, nor any
standard way to do so in various RDBMSs.
I was wondering why we need to have foreign keys *exactly match* referenced primary keys. In
MySQL, it is possible to have a foreign key reference part of an index (top part, not tail end).
This way, we can have a foreign key "[partyId, contactMechId]" from PartyContactMechPurpose to
PartyContactMech. We can do application-level (EECA) checks for fromDate. Currently, OFBiz
entities don't have foreign keys at all when it encounters fromDate entities!
Also, why don't we allow foreign keys to reference indexes on referenced entities? Restricting
foreign keys to referencing primary keys only seems a bit inflexible.
Speaking of application-level (EECA) checks for fromDate, is it a good idea to have some automatic
checks at the Entity Engine level? Much like how fields "lastUpdatedStamp", "lastUpdatedTxStamp",
etc, are handled. I know it's slower than using "ON UPDATE CASCADE". But not all RDBMSs have that,
I think. We can always have a switch to toggle on/off the application-level checks for fromDate
fields.
Speaking of "ON UPDATE CASCADE", should we allow an attribute in <relation> for this? Do we also
want to auto-detect the RDBMS being used, and scream a warning if we encounter one that doesn't
support "ON UPDATE CASCADE"?
Am I missing a whole chunk of the Entity Engine? Or is this a commonly wished
wishlist?
Jonathon
Jonathon -- Improov wrote:
Ah, wait. Note one more thing.
In entity PartyContactMechPurpose, there is supposed to be a type "one"
relation to PartyContactMech. Why is it missing? We could have a field
like "partyContactMechFromDate", so it doesn't clash with "fromDate".
Same for "thruDate". Of course, that would mean we cannot easily change
the "fromDate" field in PartyContactMech (unless we do EECA).
As it is now, it is possible to actually get PartyContactMechPurpose to
point to a non-existent PartyContactMech. Just mix up the partyId and
contactMechId such that no such combination exists.
Advice? Or should we live without foreign key checks in such cases?
Jonathon
Jonathon -- Improov wrote:
I found this from David Jones:
> Foreign keys are done for type "one" relationships, not type many.
A type
> "many" relationship is usually just the reverse direction of a type
"one"
> relationship so the FK covers both.
>
> What would it mean to have a foreign key on a type "many"
relationship?"
Then a corresponding "one" relation will supply the foreign key
constraint?
What about a many-to-many relation? Look at entity PartyAttribute and
PartyTypeAttr for example. Is that a many-to-many? Looks odd, though.
A many-to-many usually requires a separate "match-make" table.
Is it correct to say that OFBiz does not do foreign key constraints
with type "many" relations?
Do we have to insert an additional type "one" relation on field
"attrName" in order to get the foreign key constraints checks? But can
a "one" relation be created without specifying the full primary key?
Looks like the type "many" relation is merely a convenient means to do
a query like "where attrName = whatever", so we can do a simple
getRelated().
Jonathon
