Here is an interesting paradox that I’ve been dealing with…
We have a MySql set of tables with 3 table in a typical parent–>child–>grandchild arrangement. The tables have foreign key constraints established, an example of which is shown below:
CONSTRAINT staff_ibfk_1 FOREIGN KEY (fk_dept_id) REFERENCES departments (pk_dept_id) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
In Servoy I can enter the Relations dialog and click the Create button. That will automatically create relations in Servoy that are consistent with foreign key constraints. However the relations do no recognize the ON DELETE CASCADE parameter.
Now I can understand that Servoy may not replicate that parameter, as that would cause Servoy to try to delete all related child records, when in fact the MySql db will do that automatically. There is no sense in having Servoy attempt an operation that is not necessary.
However, when the backend DB does delete the child records automatically, Servoy does not know that it has happened.
So I’m curious… have others run into this situation? How have you handled it?
I think there is a more basic question behind yours. What do you want your database to do and what do you want to deal with from within your solution.
To be honest I have no clear view on that question yet. Although, I lean towards the fact that I like to be ‘in control’ via the solution. That way it is me that can ‘play’ with the rules instead of the database. Solves your issue as well…
On the other hand that way you will loose the ability to delete a parent record easilty from any other application but then again that would be the same as having your database taking care of that.
Hope this helps a little (or contributes to a nice discussion with all sorts of opinions)…
since Servoy deletes the parent, it won’t be able to get to the childs anyway, so the fact that Servoy isn’t really aware that the child records have been deletes, shouldn’t be an issue.
Our experience is the same as pbakker, as long as most of your solution is based off of the parent table, showing tabs of the child and grandchild tables. However if you directly browse the child tables in Servoy, without going through the parent table, you will probably have a problem. So, it kind of depends on how your solution uses the tables.
If you are searching or loading records based with RawSQL, then you could just always make sure to add a Join to the parent table in your sql when loading or searching the child or parent tables…since servoy knows the record in the parent table is deleted.