Fails to execute deletion of foreign key linked entries

[JuanSantosGomez]

First of all thank you for this wonderful plugin. This is a really great tool and this really opens up a lot of possibilities for beginners such as myself to delve into the nitty gritty stuff of game development. Awesome job! :)

Anyways, here's my little issue:

I've setup two tables with a foreign key field on one table set to ON CASCADE DELETE . When executing a delete query from the sqlite browser, to delete an entry from the parent table, the entry on the child table gets deleted. However, when I run the same query on Godot with the plugin using the query() function, the child table entry that's linked through the fk is not deleted.

Here's the create statements for context:

CREATE TABLE "skills_details" (
    "id"    INTEGER NOT NULL UNIQUE,
    "skill_id"  INTEGER,
    "description"   TEXT,
    CONSTRAINT fk_skillid
        FOREIGN KEY(skill_id) REFERENCES skills_skills(id) ON DELETE CASCADE,
    PRIMARY KEY("id" AUTOINCREMENT)

)

CREATE TABLE "skills_skills" (
    "id"    INTEGER NOT NULL UNIQUE,
    "name"  TEXT,
    "child_id"  INTEGER,
    "parent_id" INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT)
)

and the delete query,

DELETE FROM skills_skills WHERE id is 12

Steps to reproduce:

1. Install Godot v3.4.3.
2. Create a db using the above create statements.
3. Populate the two tables and make sure to have a linked entry on skills_details to an entry on skills_skills by having skills_details skill_id match the id on skills_skills.
4. Create a new Godot Project and add the godot-sqlite plugin.
5. Create a node and attach a script with the following content:

extends Node2D
const SQLite = preload("res://addons/godot-sqlite/bin/gdsqlite.gdns")
var db
var db_name = "res://DataStore/database" # where you placed your db

func _ready():
    commitDataToDB()

func commitDataToDB():
    db = SQLite.new()
    db.path = db_name
    db.open_db()
    db.query("delete from skills_skills where id is 1") #1 could be any id that's linked to a foreign key on skills_details table
    db.close_db()

6. Run it. Observe if the entry on skills_details got deleted.

[2shady4u]

Hello @JuanSantosGomez

I've done some experimenting myself and it seems that this issue is fixed if you enable fk support by adding following line before opening the database:

db.foreign_keys = true

Most likely SQLiteStudio enables foreign keys for the database automatically whenever it detects that you are using fk in any of your tables. This is something I could definitely look into automating as well.

[JuanSantosGomez]

Oh it works! thanks @2shady4u !

So I tried turning on fk through the query statement before this as well but it didn't work. It's good to know we can do it the way you did it.

It seems like this is an oversight on my part as it seems this is also stated in the documentation on the front page. Apologies for that, and thank you for spending time on this. Have a great weekend! ;)

[skysphr]

Ran into the same problem, and it can also be fixed by querying PRAGMA foreign_keys=ON after opening the database.