fedeteka fedeteka - 3 months ago 8
Vb.net Question

Enabling Foreign Key Support for SQlite on VB Net

Following this tutorial http://www.sqlite.org/foreignkeys.html I need a database with 3 tables but a I can't fix a message about "SQL logic error.... near Foreign"

Private Sub CreateDataBase()

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

Try
Using (conn)
conn.Open()


'3 Tables I need to create
Dim mainTable = "CREATE TABLE IF NOT EXISTS users (userID INTEGER PRIMARY KEY, name VARCHAR(20))"
Dim tableA = "CREATE TABLE IF NOT EXISTS tableA (ItemA VARCHAR(20), user INTEGER) FOREIGN KEY(user) REFERENCES users (userID)"
Dim tableB = "CREATE TABLE IF NOT EXISTS tableB (ItemB VARCHAR(20), user INTEGER) FOREIGN KEY(user) REFERENCES users (userID)"

Dim cmdConexion As SQLiteCommand = New SQLiteCommand(mainTable, conn)


'Try for the mainTable
Try
cmdConexion.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString())
End Try


'Set and Try for tableA
cmdConexion.CommandText = tableA
Try
cmdConexion.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString())
End Try


'Set and Try for tableB
cmdConexion.CommandText = tableB

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

End Using

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

End Sub


The same code works fine for only the mainTable.

I tryed with a line like

Pragma("foreign_keys") = 1


or even

cmdConexion.CommandText = "PRAGMA foreign_keys = ON"


but still I can´t fix the error.

Where is the error on the SQL line or on the Pragma on?

Note:
Also, is there another way to execute the SQL Create Table on the same Try - Catch or is better one Try - Catch for each table?

Answer

You have a syntax error int the CREATE TABLE statement. The FOREIGN KEY-part must be within the parentheses

CREATE TABLE IF NOT EXISTS tableA (
    ItemA VARCHAR(20),
    user INTEGER,
    FOREIGN KEY(user) REFERENCES users (userID)
)

or simply

CREATE TABLE IF NOT EXISTS tableA (
    ItemA VARCHAR(20),
    user INTEGER REFERENCES users (userID)
)

Note: an err like "... near something" is often just a syntax error.

Comments