fedeteka fedeteka - 2 months ago 20
Vb.net Question

How to add PRAGMA foreign_keys = ON for SQLite on VB Net?

Working on VisualStudio with SQlite

I create my tables with

Dim mainTable = "CREATE TABLE IF NOT EXISTS users (userID INTEGER PRIMARY KEY, type VARCHAR(20))"

Dim tableA = "CREATE TABLE IF NOT EXISTS tableA (partA VARCHAR(20), userID INTEGER, FOREIGN KEY(user) REFERENCES users(userID) ON DELETE CASCADE)"


On the sub where I want to delete on cascade I know the value of userIDtoDelete (previously selected on a DataGrid) and I have this connection

Dim connDeleteUser = New SQLiteConnection("Data Source=MyDataBase.sqlite;Version=3")

Try
Using (connDeleteUser)

connDeleteUser.Open()


Dim sql = "DELETE FROM actos WHERE userID = ?"

Dim connDeleteUser As SQLiteCommand = New SQLiteCommand(sql, connDeleteUser)

connDeleteUser.CommandText = sql

connDeleteUser.Parameters.AddWithValue("@userID", userIDtoDelete)

connDeleteUser.ExecuteNonQuery()

'I don´t know if this line is neccesary
connDeleteUser.Close()


End Using

Catch ex As Exception
MsgBox(ex.ToString())
End Try


I know I need to declare PRAGMA foreign_keys = ON but I can´t figure out how

I tryed things like:

PRAGMA foreign_keys = ON

connDeleteUser.PrepareRetries(@"PRAGMA foreign_keys = ON)

connDeleteUser.CommandText = "PRAGMA foreign_keys = ON"

Dim sql = "DELETE FROM users WHERE userID = ? PRAGMA foreign_keys = ON"

CL. CL.
Answer

The PRAGMA is an SQL statement; it must be executed like any other SQL statement:

someCommand.CommandText = "PRAGMA foreign_keys = ON"
someCommand.ExecuteNonQuery

Anyway, you can simply add the Foreign Keys = True option to the connection string.

Comments