Do not allow the deletion of the primary key data if it has relationship
-
I'm using the QSqlRelationalTableModel model / view to manipulate my database.
In my database, I have a primary key in a T1 table that is the foreign key in another T2 table, the relationship between the two is ON DELETE Restricted.
1- when I try to delete the data of the primary key, it forbids me -> OK (what I want)
2- when I use the QSqlRelationalTableModel via my application, it deletes the data of the primary and foreign key -> NOK.How to force my model / view to respect the rules of the database?
Thanks in advance.
-
I'm using the QSqlRelationalTableModel model / view to manipulate my database.
In my database, I have a primary key in a T1 table that is the foreign key in another T2 table, the relationship between the two is ON DELETE Restricted.
1- when I try to delete the data of the primary key, it forbids me -> OK (what I want)
2- when I use the QSqlRelationalTableModel via my application, it deletes the data of the primary and foreign key -> NOK.How to force my model / view to respect the rules of the database?
Thanks in advance.
@Oussama-YENNOUNE
QSqlRelationalTableModelknows nothing about your database-side rule for DELETE. (Indeed, it does not know you have any database support for deletion.) It implements a relationship purely client-side, and so implements a delete by sending two DELETEs to the database to achieve what it understands you want.So it's up to you to code in whatever rules you need to simulate the behaviour you want at the database, e.g. you must forbid the delete client-side if that's what you want.
-
OK, thanks.
So, I do that in the function: setModelData? -
OK, thanks.
So, I do that in the function: setModelData?@Oussama-YENNOUNE
What does this "ON DELETE Restricted" do? I come from MS SQL Server, no such thing. I'm guessing some SQL server side rule which forbids deleting PK row if it's in use in FK row, is that it?? -
@Oussama-YENNOUNE
What does this "ON DELETE Restricted" do? I come from MS SQL Server, no such thing. I'm guessing some SQL server side rule which forbids deleting PK row if it's in use in FK row, is that it?? -
@JonB
I use SQLITE, yes exactly, that's it.
would it still be necessary, that I repeat the Qt client check, to make sure that the primary key is not used, knowing that the rule of the database already does it?@Oussama-YENNOUNE
Previously you wrote:2- when I use the QSqlRelationalTableModel via my application, it deletes the data of the primary and foreign key -> NOK.
How to force my model / view to respect the rules of the database?
My thought would be you are responsible for enforcing this if you are finding it goes into the "NOK" case.
For example, I don't know whether
QSqlRelationalTableModeldoes it this way round, but if when you tell it to delete the PK row it (chooses to) generate aDELETEof the corresponding FK record first, followed by aDELETEof the PK record second, then this will by-pass the intention of your "ON DELETE Restricted", won't it (because by the time it comes to delete the PK row it will already have deleted the FK row)? Is this why you are currently seeing the "NOK" case?So in your code allowing deletion of PK rows I think you need to make a check for the FK existence and forbid the delete of the PK row if it does. Maybe use http://doc.qt.io/qt-5/qsqltablemodel.html#removeRows to throw an error, say.
Bear in mind I have not done any of this! It's my guess as to what you want/is required.
-
@Oussama-YENNOUNE
Previously you wrote:2- when I use the QSqlRelationalTableModel via my application, it deletes the data of the primary and foreign key -> NOK.
How to force my model / view to respect the rules of the database?
My thought would be you are responsible for enforcing this if you are finding it goes into the "NOK" case.
For example, I don't know whether
QSqlRelationalTableModeldoes it this way round, but if when you tell it to delete the PK row it (chooses to) generate aDELETEof the corresponding FK record first, followed by aDELETEof the PK record second, then this will by-pass the intention of your "ON DELETE Restricted", won't it (because by the time it comes to delete the PK row it will already have deleted the FK row)? Is this why you are currently seeing the "NOK" case?So in your code allowing deletion of PK rows I think you need to make a check for the FK existence and forbid the delete of the PK row if it does. Maybe use http://doc.qt.io/qt-5/qsqltablemodel.html#removeRows to throw an error, say.
Bear in mind I have not done any of this! It's my guess as to what you want/is required.
@JonB
okay, that's what I'm looking.
thank you JonB for your help.Regards,
