There are 4 extra tables next to the main (stories) table: Characters, Events, Locations and Write.
If you do:
PRAGMA foreign_keys;
do you get a 1?
Yes.
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
And it must be enabled on the connection you are using:
db.ExecuteSQL ("PRAGMA foreign_keys = ON")
as discussed here (see section 2):
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
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.
Musnât you use db.ExecuteSQL instead ?
But I do not think this will remove your troubleâŠ
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.
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)
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.
You use it when you connect to the database, not when creating the tables.
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