There are 4 extra tables next to the main (stories) table: Characters, Events, Locations and Write.
If you do:
do you get a 1?
Are you setting:
PRAGMA foreign_keys = ON;
Did you run
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