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.



  • @Oussama-YENNOUNE
    QSqlRelationalTableModel knows 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?



  • @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 QSqlRelationalTableModel does it this way round, but if when you tell it to delete the PK row it (chooses to) generate a DELETE of the corresponding FK record first, followed by a DELETE of 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,


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.