|
We are using SQL Anywhere v12.01.3554. What impact does changing primary key on a record have when done on remote side? Here is our situation: 1. Record inserted into table. INSERT INTO DBA.pi_count(company,brand,size,ticket,date_entered, time_entered,user_name,computed_cases) VALUES (30,0,0,176468,'2014-09-01 00:00','19:48:29.0','Dustyg',0) The values for brand, size, computed cases are not known at time of insert. Primary key in this example is company, brand, size, ticket.
UPDATE DBA.pi_count SET brand=489 WHERE company=30 AND brand=0 AND size=0 AND ticket=176468
This is not what I was expecting. Can anyone provide an explantion as to what is going on here? I am guessing that my issue is updating the primary key and this is frowned upon in a MobiLink setup so I will likely need to re-design my table to avoid doing this in the future. This is the only table in our setup where we update the primary key after initial setup. Thanks for any help. Brian |
|
Your expectation is correct, just have a look at these generell MobiLink development tips in the docs
...it doesn't even mention "unique constraint" which is OK for a foreign key, it's gotta be a primary key. MobiLink is so strict...
(04 Sep '14, 11:09)
Breck Carter
1
...but it's for your own good, even if it doesn't feel like it at the time. ;-)
(05 Sep '14, 09:20)
RussC_FromSAP
|
|
Just to add:
From MobiLink's point of view, a surrogate PK with no real meaning would certainly help to avoid the need to update a PK lateron. However, you state:
So if there's a need that these values are unique (even when not used as PK and possibly not declared as UNIQUE KEYS) and will be filled lateron - how do you make sure no two remotes can set the same unique values for different rows? I'm just hinting at the fact that modifying PKs in a ML setup is no good but guaranteeing non-PK UNIQUE constraints in a distributed system is not that easy, either... - it might require some kind of conflict resolution, methinks. 1
> guaranteeing non-PK UNIQUE constraints in a distributed system is not that easy, either Indeed... it is the kind of thing that can cause an upload to fail, completely disabling further synchronization for the offending remote database. One workaround is to omit the UNIQUE constraint from the consolidated database. The real solution is to sit down and think carefully about the database design, away from distractions... it's not the job for a committee, the committee isn't there at 4AM when MobiLink stops working.
(08 Sep '14, 08:06)
Breck Carter
|
One thing that is confusing for me is why does consolidated have inserted row with brand updated (combination of initial insert statement and brand update). All updates after that are not synched.
Caveat: I have not explored what actually happens when a remote primary key value is changed.
[guesswork] The MobiLink synchronization process uses the primary key to identify each row uniquely. The upload process may interpret a new primary key value as an insert when determining what to upload, hence the new row on the consolidated. You may end up with an old unchanging row and a new row subject to future changes coming from the remote, on the consolidated. I will put this on my research to do list, right after "Achieve World Peace" :) [/guesswork]