Re: M:N-like table with a contents, updating the PK leads to problems
Re: M:N-like table with a contents, updating the PK leads to problems
- Subject: Re: M:N-like table with a contents, updating the PK leads to problems
- From: mailinglists via Webobjects-dev <email@hidden>
- Date: Tue, 11 Oct 2022 11:55:42 +0200
Thou shall never ever change a primary key!
Replace the compound PK with a “real” ID and be done. This is not such a big
thing to do. I've done this several times. You need a bit of SQL to “fix” your
database, but that is no rocket science.
This m:n join table is not a mere technical requirement anymore but now
represents business logic so it really really should have its own dedicated
primary key.
At least that’s how I would do it.
Good luck
---markus---
> On 11 Oct 2022, at 00:16, OCsite via Webobjects-dev
> <email@hidden> wrote:
>
> Hi there,
>
> I've just bumped into a new problem. There's a table which, many years ago,
> was created as an invisible M:N intermediate table. Later, we needed to add
> some information to the relationship, so now we have a table, say,
> Connection, which has
> - a number of normal attributes
> - a compound PK (department_id, user_id) which contains two FKs into two
> other tables, say, User and Market (the remaining of the original M:N
> intermediate)
> - two :1 relationships to those two tables (user and market).
>
> Both User and Market tables model :N relationships connections (owning,
> PK-propagating), which long long ago replaced the original flattened M:N
> ones. Worked like a charm for years.
>
> Now though, I've got a new requirement: I need to be able to change the user
> of a given Connection.
>
> I've found that
> aConnection.addObjectToBothSidesOfRelationshipWithKey(newUser,'user') seems
> to work sort of properly — looks like all the relationships are properly
> updated and the key in the Connection table is changed in the database all
> right.
>
> The catch is, sometimes (by far not always), a short time after the change, I
> start getting
>
> No Connection found with globalID: <Connection: [department_id: X, user_id:
> Y] >
>
> with the original pre-change values of X and Y.
>
> I can't be quite sure, but I think probably there's sometimes a :N
> User.connections snapshot which contains the globalID of the original
> object. Since the user relationship change of its target actually changes the
> very PK of the object, the EOF synchronisation does not match the updated
> object (with a different PK => different globalID) with the original one and
> does not update the snapshot. Then, someone touches the relationship, gets
> the snapshot, EOF creates a fault with the original values, and when the
> fault fires, oops, there's nothing like that in the database.
>
> Does anybody see how to fix the problem?
>
> In principle I guess I could go programmatically through all the :N snapshots
> and try to find the old globalIDs and replace them by the new ones; but it
> would be sorta non-trivial and definitely dangerous...
>
> Thanks,
> OC
>
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden