Android Question Deleting the records from the Master Table in SQLLite does not delete the records from the Transaction table despite the presence of Foreign Key - reg

beelze69 · Mar 12, 2022

Hi,

I have a b4A application with Sqllite database.

I have 2 tables in Sqllite.. 1 Master Table (say tbl_Master) and 1 Transaction Table.

The Transaction Table is created with

...Constraint fk_KeyName Foreign Key(the_ID) References tbl_Master(the_ID) on delete cascade...

But when I delete the records from the Master Table, the records in the Transaction Table does not get deleted.

Note: I am using 'Delete from tbl_Master where the_ID=<key value>'

1) Whether I have to use 'Delete Cascade'and if yes.. what is the right way to use it ?

2) Documentation for Sqllite says that the 'Pragma foreign_keys=on' has to be set for every session before the Delete is executed... So how does one set a 'Pragma foreign_keys=on' always and not for every session for the SQLLITE database residing inside the Mobile?

3) If it is not possible to do the above Pragma setting permanently for the sqllite db residing inside the APP, then how should one execute 'Pragma foreign_keys=ON' followed by the 'Delete command' in a b4A app ?

Requesting for guidance on the same,

Thanks

Mahares · Mar 12, 2022

beelze69 said:
So how does one set a 'Pragma foreign_keys=on' always and not for every session

You can check the status of your foreign key and turn it on if off withy this code:

B4X:

Dim MyForeignKey As String
    MyForeignKey=SQL1.ExecQuerySingleResult("PRAGMA foreign_keys")
    If MyForeignKey = 0 Then    '0=OFF;  1=ON
        SQL1.ExecNonQuery("PRAGMA foreign_keys=ON")  'Change it to ON if it was 0 (OFF)
    End If

If you continue to have problems deleting, I think you should post all the code inside code tags , not the way you have it in your above post to create the 2 tables, the delete statement and an example. It is even better if you create a tiny project to reproduce the issue, because foreign key, cascade work if properly applied.

beelze69 · Mar 12, 2022

Mahares said:
You can check the status of your foreign key and turn it on if off withy this code:

B4X:

Dim MyForeignKey As String MyForeignKey=SQL1.ExecQuerySingleResult("PRAGMA foreign_keys") If MyForeignKey = 0 Then '0=OFF; 1=ON SQL1.ExecNonQuery("PRAGMA foreign_keys=ON") 'Change it to ON if it was 0 (OFF) End If

If you continue to have problems deleting, I think you should post all the code inside code tags , not the way you have it in your above post to create the 2 tables, the delete statement and an example. It is even better if you create a tiny project to reproduce the issue, because foreign key, cascade work if properly applied.

Thanks Mahares,

I will try this out.

And if does not work, I will post the code..

Also, can you please guide me on my Doubt 1) viz. the right way to use 'Delete Cascade'...Because when I tried the sql query via DBBrowser simply setting the Foreign_Keys=on worked with ordinary Delete command (meaning there was no need to use Delete Cascade) ...So I wanted to know when and how to use Delete cascade the right way...

Thanks..

Mahares · Mar 12, 2022

beelze69 said:
the right way to use 'Delete Cascade

When you try to delete records from the tbl_Master table when there are dependent records in the tbl_Transation table without the Cascade delete, it will not delete any records from tbl_Master. But if there are no dependent records in tbl_Tarnsaction, it will delete the master records.

beelze69 · Mar 12, 2022

Mahares said:
When you try to delete records from the tbl_Master table when there are dependent records in the tbl_Transation table without the Cascade delete, it will not delete any records from tbl_Master. But if there are no dependent records in tbl_Tarnsaction, it will delete the master records.

Hi Mahares,

Thanks for the guidance.

The records in the Transaction Table automatically got deleted by using an 'ordinary delete' on the 'master table' when I used your Pragma Subroutine...
I used ' DBUtils.DeleteRecord' to delete the records in the master table and the relevant records in the transaction table also got deleted automatically which is what I wanted...

Anyway, I wanted to know the right syntax for Delete Cascade...

Thanks...

Mahares · Mar 12, 2022

beelze69 said:
DBUtils.DeleteRecord'

I don't use DBUtils library for any of my code.

beelze69 said:
I wanted to know the right syntax for Delete Cascade...

You add ON DELETE CASCADE when you are creating the child table (tbl_Transaction) not when you are deleting.
It is better If you show your statements and your columns names, it would be easier to correct you if you have some doubt.

beelze69 · Mar 13, 2022

Mahares said:
I don't use DBUtils library for any of my code.

You add ON DELETE CASCADE when you are creating the child table (tbl_Transaction) not when you are deleting.
It is better If you show your statements and your columns names, it would be easier to correct you if you have some doubt.

Thanks Mahares,

I think I got a bit confused about this 'DELETE CASCADE' thing ... Actually ON DELETE CASCADE is used while creation of the table which I have already done ('please refer my 4:18 pm post yesterday in which I have mentioned that I have used ON DELETE CASCADE while creating my transaction table)... However, I actually have never used 'DELETE CASCADE' ... The issue was that when I was trying something on the 'DBBrowser utility for SQLLITE', when I typed DELETE the intellisense showed CASCADE following it ... so was under the impression that there exists a 'DELETE CASCADE' command also and was wondering how to use a 'DELETE CASCADE' .. I checked the net for using a 'DELETE CASCADE' command and did not find any ... Hence the confusion.. Thanks..

Android Question Deleting the records from the Master Table in SQLLite does not delete the records from the Transaction table despite the presence of Foreign Key - reg

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Similar Threads

Android Question Deleting the records from the Master Table in SQLLite does not delete the records from the Transaction table despite the presence of Foreign Key - reg

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Mahares

Expert

beelze69

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency