We’ve got a bad situation where records are disappearing from other connected clients immediately after being changed in one client. We think this is related to the fact that the column being changed is part of the primary key for the table.
The schema is one person has many degrees, ie: people.id_people (pk) --one-to-many–> people_degrees.id_people (pk) people_degrees.id_degree_type (pk) <—many-to-one-- degrees.id_degrees (pk). (Note there is no auto-increment via Servoy or the database in the people_degrees join.)
We have a value list equal to all rows in the degrees table which returns the people_degrees.id_degree_type value in the people_degrees join table. When the combobox/valuelist is changed in one client, that record disappears in the other clients. The Servoy relation under people is “people have degrees; people.id_people = people_degrees.id_people”, and is database-derived.
Can anyone comment on if this schema setup is causing records to disappear on change to a pk value? This is a key issue for us, so any help is very much appreciated. Happy to provide more info. if needed.
Am I understanding you right when you say the primary key value is being changed? IF I am understanding you right, pk values should never be editable especially by clients. That will cause all kinds of havoc, including losing records.
The primary key IS being changed, but not by the user – but by a database trigger. The problem is, since it is happening outside of Servoy, the clients aren’t picking up the change, so it looks to then as if the record disappeared.
(Thanks Glenn… actually in this case the end user does change part of the PK.)
In response to Firestorms’s comment (thanks!), the schema we are using on the join table has considerable benefits over one with an auto-incrementing field as uniqueness is ensured by the composite key spanning the join keys. (The alternative is more overhead.)
Other notes:
The record is only lost in the clients other than where the change is made. Why isn’t it lost in the client making the change as well?
Servoy knows the value being changed is a PK as it’s specified in dataproviders. If it’s a bad idea, seems Servoy shouldn’t permit it.
As I mentioned, the Servoy relationship is people.id_people → people_degrees.id_people, so it doesn’t seem like changing the people_degrees.id_degree_type field should matter at all.
Can anyone from Servoy let us know if the behaior we’re seeing is expected?