Colleague, I have small database and C++ program with sqlite3 library.
There are several table with connection by Foreign key:
CREATE TABLE companies (
id INTEGER PRIMARY KEY
UNIQUE
NOT NULL,
name TEXT NOT NULL
UNIQUE,
userId INTEGER REFERENCES userlist (id) ON DELETE RESTRICT
);
CREATE TABLE drivers (
id INTEGER NOT NULL
UNIQUE,
surName TEXT,
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
companyId INTEGER REFERENCES companies (id) ON DELETE CASCADE,
PRIMARY KEY (
id AUTOINCREMENT
)
);
CREATE TABLE whiteList (
id INTEGER NOT NULL
UNIQUE,
idName INTEGER NOT NULL
REFERENCES drivers (id) ON DELETE CASCADE,
carNumber CHAR (9) NOT NULL
UNIQUE,
PRIMARY KEY (
id AUTOINCREMENT
)
);
CREATE TABLE access (
id INTEGER PRIMARY KEY AUTOINCREMENT
UNIQUE,
carId INTEGER NOT NULL
REFERENCES whiteList (id) ON DELETE CASCADE,
zoneId INTEGER NOT NULL
REFERENCES zones (id) ON DELETE CASCADE,
UNIQUE (
carId,
zoneId
)
);
There is pragma before DELETE execution in C++ code.
rc = sqlite3_exec(db, "PRAGMA foreign_keys = ON;", NULL, NULL, &zErrMsg);
But when I try delete company, ON DELETE CASCADE doesn’t work at all.
When I try delete driver, I can delete car only together. Access doesn’t change.
So, understand anyone why?
Please help.