# Updating Parent and Childs with Primary key **URL:** \ **Category:** questions\ **Tags:** mssql, tps, fm3\ **Created:** [May 22, 2024, 8:35am UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182 "2024-05-22T08:35:14Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\ **Post date:** [May 22, 2024, 8:35am UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/1 "2024-05-22T08:35:15Z")
Situation: Clarion 11.0.13630 FM3 Multi-Project I made a desktop app with Multi-Project driver substitution (TPS and MS-SQL). So i have a TPS version and a MS-SQL version now, that works great. Both have the same dct with all te relations set in the dct. Problem: In the **TPS version** when i change a unique key (which is also a primary key), the field (Parent) is updated as well as all the child records, with that same field, based upon the dct relations. When i do the same in the **MS\_SQL version** i get the error _changed by another station_ and it shows again the field prior to the changes. So no update takes place. Question: Is there a way to make the MS-SQL version react the same as in the TPS version? That means updating the unique (Parent) field and also all the same child fields. I did set /MULTIPLEACTIVERESULTSETS=TRUE Thanks in advance. Rob ---
**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\ **Post date:** [May 22, 2024, 10:10am UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/2 "2024-05-22T10:10:20Z")
Changed by another station is usually a result of server stored time stamps vs clarion. Clarion is only accurate to 100th of a second many time stamps are to the 1000th. and .999 \<\> .9999 so clarion objects. There is a way to define the time/datetime to the appropriate accuracy, but I’m unsure on mssal how to do that. ---
**Author:** ![Niels\_Larsen](https://clarionhub.com/user_avatar/clarionhub.com/niels_larsen/32/655_2.png) [@Niels\_Larsen](https://clarionhub.com/u/Niels_Larsen)\ **Post date:** [May 22, 2024, 10:17am UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/3 "2024-05-22T10:17:24Z")
If possible, my advice is to use surrogate keys. This way you avoid changing your primary keys. Life just gets so much easier. /Niels ---
**Author:** ![rob\_bac](https://clarionhub.com/user_avatar/clarionhub.com/rob_bac/32/686_2.png) [@rob\_bac](https://clarionhub.com/u/rob_bac)\ **Post date:** [May 22, 2024, 10:59am UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/4 "2024-05-22T10:59:21Z")
There are no time or datetime fields involved. ---
**Author:** ![Mark\_Sarson](https://clarionhub.com/user_avatar/clarionhub.com/mark_sarson/32/6132_2.png) [@Mark\_Sarson](https://clarionhub.com/u/Mark_Sarson)\ **Post date:** [May 22, 2024, 12:03pm UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/5 "2024-05-22T12:03:46Z")
I would add to @Niels_Larsen post. IMHO: If you are having to change a primary identifier, then there is something wrong with your design. The identifiers should only ever get set when a record is created and should never be changed. If you are changing, then this probably means you are in need of a separate field for the data. So an explanation as to why you are having to change this might be useful. Regards Mark ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [May 22, 2024, 12:17pm UTC](https://clarionhub.com/t/updating-parent-and-childs-with-primary-key/7182/6 "2024-05-22T12:17:19Z")
Assuming you have a form on the parent field, an the cascade (NOT server) set in the dictionary, what happens is: Clarion loops through all the child records belonging to the parent, changing the parent field. If that all works then it changes the parent. In order for this to work in MS-SQL, assuming that there is a foreign key set up on the parent\_ID in the child, you would have to use deferrable constraints. That is, you have to tell the foreign key constraint not to check that its parent exists until the commit happens. If you don’t do that, then when you try to change the first child record, the server will tell you it can’t because you have a foreign key constraint violation, and it has changed no records. I would have thought you would get the FK violation from the server reported, but if that didn’t happen, then the “Record changed..” error would be a response to no records being changed when it expected to change one. Personally, I’m with Niels and Mark, and think changing linking fields is a horrible idea.