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]

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]

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:

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]

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

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]

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]

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]

DBUtils.DeleteRecord'

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

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]

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..