SQLite: Foreign Key

Databases
1

This code generate an error


SQL_Cmd = "CREATE TABLE IF NOT EXISTS SessionPersonnel("+_
"Personnel_ID INTEGER NOT NULL FOREIGN KEY, " +_
"Session_ID   INTEGER NOT NULL FOREIGN KEY, " +_
"SessionPersonnel_RoleInSession VARCHAR, "     +_
"PRIMARY KEY(Personnel_ID, Session_ID))"

image
image744×528 49 KB

I nearly forgot:

db.Connect

// Not enabled by default, but I need it !
db.ExecuteSQL("PRAGMA foreign_keys = ON;")

these appears before the code with Syntax Error.

2

Im pretty sure if you say ‘this is a foreign key’ then you need to explain where the key it refers to lives - ie which other table and which other column.

image
image1416×696 40 KB

It will work perfectly well without the words FOREIGN KEY.

// Not enabled by default, but I need it !

You may be right, but why?

3

The documentation says that, so I add the code. Without example(s), it is terra incognita for me.

Thank you.

4

Do you need this:

perhaps?

5

No, it’s in SQLiteDatabase:

Foreign keys

SQLite supports foreign keys, but they are not enabled by default. To enable them, you use the SQLite PRAGMA command each time you connect to the database:

PRAGMA foreign_keys = ON;

You can send this PRAGMA command to SQLite from Xojo using ExecuteSQL:

Var db As New SQLiteDatabase

Try
  db.Connect
  ' Enable foreign keys
  db.ExecuteSQL("PRAGMA foreign_keys = ON;")
Catch error As DatabaseException
  MessageBox("Database error: " + error.Message)
End Try
6

That was why I mentioned it.

7

Try this SQL command

CREATE TABLE IF NOT EXISTS SessionPersonnel
(
Personnel_ID INTEGER NOT NULL,
Session_ID   INTEGER NOT NULL,
SessionPersonnel_RoleInSession VARCHAR,
PRIMARY KEY (Personnel_ID),
FOREIGN KEY (Session_ID) REFERENCES Session_ID
)

Why do you nee two primary keys ?

8

Issues in Your SQL Statement

  1. Incorrect FOREIGN KEY Syntax
    In SQLite, FOREIGN KEY constraints are not specified directly in the column definition with just FOREIGN KEY. You must use REFERENCES to specify which table and column the foreign key relates to.
  2. Missing comma before PRIMARY KEY
    There is no comma between the last column definition and the PRIMARY KEY line.
  3. SQLite doesn’t use VARCHAR without a length (though it allows it)
    While SQLite allows VARCHAR with no length, it’s more typical to just use TEXT.

Corrected Version for SQLite

CREATE TABLE IF NOT EXISTS SessionPersonnel (
    Personnel_ID INTEGER NOT NULL,
    Session_ID   INTEGER NOT NULL,
    SessionPersonnel_RoleInSession TEXT,
    PRIMARY KEY (Personnel_ID, Session_ID),
    FOREIGN KEY (Personnel_ID) REFERENCES Personnel(ID),
    FOREIGN KEY (Session_ID) REFERENCES Session(ID)
);

Make sure that:

  • You have tables named Personnel and Session with ID as their primary keys.
  • Foreign key support is enabled in your SQLite database (run PRAGMA foreign_keys = ON;).
9

join table

10

From the current doc.



Var sql As String = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"

Yes they are. I am currentlyu building (and run here and there).

Thabk You.