Cascading foreign keys are not working about sqlite HOT 3 CLOSED

So this issue is not so much an issue with this Go package so much as a subtlety to both PRAGMA foreign_keys = ON and sqlitex.ExecScript.

First understand that sqlitex.ExecScript wraps the entire script in a SAVEPOINT.

You are running PRAGMA foreign_keys = ON within this SAVEPOINT, and as such it will have no effect. This is a quirk of sqlite3 that is not well documented, but you can try it for yourself.

PRAGMA foreign_keys;
0
SAVEPOINT "a";
PRAGMA foreign_keys=ON;
PRAGMA foreign_keys;
0
COMMIT;
PRAGMA foreign_keys;
0
PRAGMA foreign_keys=ON;
PRAGMA foreign_keys;
1

You can address this by running PRAGMA foreign_keys = ON using sqlitex.Exec instead. You must do this once on each connection in the sqlitex.Pool each time you start the program or open a new connection.

	const PoolSize = 10
	dbpool, err := sqlitex.Open("file:./sample.db", 0, PoolSize)
	if err != nil {
		panic(err)
	}
	for i := 0; i < PoolSize; i++ {
		conn := dbpool.Get(nil)
		err := sqlitex.Exec(conn, `PRAGMA foreign_keys = ON;`, nil)
		if err != nil {
			panic(err)
		}
	}

from sqlite.

Thanks for the explanation.

from sqlite.

Note that once you set the pragma on all connections in the pool, that setting will remain on the connections and so there is no need to run it again as you pull connections of the pool later.

from sqlite.