SQLite - Foreign Keys Contraints - IOs 5

Question

it seems that Foreign Keys Constraints are supported since version 3.6.x in SQLite. The version of SQLite on IOS5.0 is 3.7.7 (found in sqlite3.h).

But when I try to insert a row in a table that has a constraint, my row is correctly inserted even if the related foreign key is not existing. I have no error.

Doing the same insert statement using apps like Navicat gives me a "Constraint violation error"

Do you know if foreign keys are supported on IOs 5 ?

Here is the Database Schema:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
)

CREATE TABLE "track" (
     "trackid" INTEGER PRIMARY KEY AUTOINCREMENT,
     "trackname" TEXT,
     "trackartist" INTEGER,
    CONSTRAINT "trackartist" FOREIGN KEY ("trackartist") REFERENCES "artist" ("artistid") ON DELETE CASCADE ON UPDATE CASCADE)

Really simple, isn't it ?

Thanks Emmanuel

Answer 1

Foreign keys are disabled by default. You have to enable them separately for each connection. The setting isn't "sticky". You have to do this every time you connect to a SQLite database.

PRAGMA foreign_keys = ON;

Odds are good that Navicat takes care of that for you. In your own code, it's your job.