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 > <webobjects-dev@lists.apple.com> 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 (Webobjects-dev@lists.apple.com) > Help/Unsubscribe/Update your Subscription: > https://lists.apple.com/mailman/options/webobjects-dev/mailinglists%40kataputt.com > > This email sent to mailingli...@kataputt.com
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
