PB5.62: SQLite "ON DELETE CASCADE" doesn't work

[Yuri_D]

Hello 2 every1)

Search was unsuccessful so I'd like to ask why subj doesn't work in PB (only the parent table's data are deleted) but it works correctly in SQLite Browsers?
(I can see that PB has SQLite v3.21.0 inside)

Any ideas are appreciated!

Yury

[infratec]

It's not a PB bug

You ask the wrong forum.
That's not PB related, it's SQLite related.

But ...

Have you done:

Code: Select all

PRAGMA foreign_keys = ON

You have to do this directly after connection to the DB.
Before any other commands.

Bernd

[Yuri_D]

Hello infratec, thanks for your hint)

Yes I've enabled foreign_keys but it makes no difference, no cascade updates occurs and I can't find any issue-related bug description for this SQLite version.
I didn't tested "ON UPDATE CASCADE" and Triggers but I think they are affected as well.
BTW the current version is 3.24.0 but I don't know how to import it to PB...

[infratec]

It works:

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  DatabaseUpdate(DB, "PRAGMA foreign_keys = ON")
  
  DatabaseUpdate(DB, "CREATE TABLE artist(artistid INTEGER PRIMARY KEY, artistname TEXT)")
  DatabaseUpdate(DB, "CREATE TABLE track(trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON DELETE CASCADE)")

  DatabaseUpdate(DB, "INSERT INTO artist VALUES (1, 'Artist 1')")
  DatabaseUpdate(DB, "INSERT INTO artist VALUES (2, 'Artist 2')")
  DatabaseUpdate(DB, "INSERT INTO artist VALUES (3, 'Artist 3')")
  
  DatabaseUpdate(DB, "INSERT INTO track VALUES (1, 'Track 1', 1)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (2, 'Track 2', 1)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (3, 'Track 3', 1)")
  
  DatabaseUpdate(DB, "INSERT INTO track VALUES (1, 'Track 1', 2)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (2, 'Track 2', 2)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (3, 'Track 3', 2)")
  
  DatabaseUpdate(DB, "INSERT INTO track VALUES (1, 'Track 1', 3)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (2, 'Track 2', 3)")
  DatabaseUpdate(DB, "INSERT INTO track VALUES (3, 'Track 3', 3)")
  
  If DatabaseQuery(DB, "SELECT artistname, trackname FROM artist, track WHERE track.trackartist = artist.artistid ORDER BY artistid, trackid")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
    FinishDatabaseQuery(DB)
  EndIf
  
  
  MessageRequester("Info", "Now I delete artist 2")
  
  Debug ""
  
  DatabaseUpdate(DB, "DELETE FROM artist WHERE artistid = 2")
  
  If DatabaseQuery(DB, "SELECT trackname, trackartist FROM track ORDER BY trackartist, trackid")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
    FinishDatabaseQuery(DB)
  EndIf
  
  CloseDatabase(DB)
EndIf

[Yuri_D]

Sorry, I already found an error(

Foreign keys enabling is a little tricky - it shall be enabled right after the DB file opened and no even a single query shall be performed between these two actions.
In my case I accidentally put a query which reads "PRAGMA foreign_keys" value before I set it to "ON" it so that's why even if the "PRAGMA foreign_keys;" query returns "1" afterwards the foreign keys constraints don't work...

[infratec]

I wrote this already in my first answer

An admin should move this to 'coding questions'

Bernd