Related records not being deleted

General
21

There are 4 extra tables next to the main (stories) table: Characters, Events, Locations and Write.

22

If you do:

PRAGMA foreign_keys;

do you get a 1?

23

Yes.

24

Are you setting:

PRAGMA foreign_keys = ON;

in Xojo?

Did you run

PRAGMA foreign_keys;

using Xojo, DB Browser or both?

From what I understand SQLite default is OFF, so I’m just trying to see if you are setting than ON with Xojo (don’t know if DB Browser sets that to ON by default).

Edit: DB Browser set the foreign_keys to ON by default
image

25

And it must be enabled on the connection you are using:

db.ExecuteSQL ("PRAGMA foreign_keys = ON")

as discussed here (see section 2):

https://www.sqlite.org/foreignkeys.html

1 Like
26

I am using this code here before creating a new database table, but this didn’t fix the problem. From what I understand this command should not be used during the Delete Row from Parent table, right? It should be place after having created the Table:

//Create a new SQLite Database
Var db as new SQLiteDatabase
db.DatabaseFile = f
If f <> Nil Then
  Try 
    db.CreateDatabase //create database
    
    db.ExecuteSQL ("PRAGMA foreign_keys = ON")
    
    //Create the tables
    db.SQLExecute(storiesTable)
    db.SQLExecute(charactersTable)
    db.SQLExecute(IdeaDrawerTable)
    db.SQLExecute(LocationsTable)
    db.SQLExecute(WriteTable)
    db.SQLExecute(EventsTable)
    //Create Notes table and add 1 record
    db.SQLExecute(NotesTable)
    db.SQLExecute ("insert into Notes (TextField ) values ('')")
    ' ("+ Winnotes.textnotes.text +")")
    //Create Preference table and add 1 record
    db.SQLExecute(PreferencesTable)
    db.SQLExecute ("insert into Preferences (ChkDoNotShowAgainStorySaved, ChkDoNotShowAgainCharSaved, QuestCannotEdited, SliderFontSize, FontSize2SystemSettings, ChkCharacterChangesSavedQ, ChkStoryChangesSavedQ) values (0, 0, 0, 14, 0, 0, 0)")
    
  Catch error As IOException
    MessageDialog.Show "An error occured creating the database" + EndOfLine + error.Message
  End Try
End If
db = Nil //set the objects to nil
27

I think it’s on by default in DB Browser, because from the start when I delete a parent table row, the child row was also deleted.

28

Musn’t you use db.ExecuteSQL instead ?
But I do not think this will remove your trouble


29

Sounds like DB Browser turns it on automatically for you. You have to do it explicitly in Xojo. There are reasons to not want it on. Sounds like you want it, so you need to turn it on.

30

I turned it on during the creation of the database if I have it correct:

//Create a new SQLite Database
Var db as new SQLiteDatabase
db.DatabaseFile = f
If f <> Nil Then
  Try 
    db.CreateDatabase //create database
    
    db.ExecuteSQL ("PRAGMA foreign_keys = ON")
    
    //Create the tables
    db.SQLExecute(storiesTable)
    db.SQLExecute(charactersTable)
31

Your db variable is local here, which means it goes out of scope at the end of the method and the connection is closed. When you open a new connection to actualy do the DELETE, you need to issue the pragma again (and every time you connect to the database).

Actually, using the pragma where you create the database does nothing. It’s only when you actually use the database that you need it.

32

You use it when you connect to the database, not when creating the tables.

33

Aaah, now I get it. Thanks. Now it works, all related data is being deleted from the db correctly. I added the code here:

Try
  db.Connect
  db.ExecuteSQL ("PRAGMA foreign_keys = ON")
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try
1 Like