ON DELETE CASCADE works only with some settings

[nik4nik]

For some reason ON DELETE CASCADE doesn't work. Refresh doesn't help. Checked in SQLiteStudio, it works there. Tools -> Foreign key check shows wrong references after deleting row from main table

This is script for my data base:

drop table "users"; drop table "friends";

PRAGMA foreign_keys=on;

CREATE TABLE users ( id INTEGER PRIMARY KEY, name VARCHAR NOT NULL );

CREATE TABLE friends ( user_id INTEGER NOT NULL, friend_id INTEGER NOT NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(friend_id) REFERENCES users(id) ON DELETE CASCADE );

INSERT INTO users (id, name) VALUES (1, "Sergey"); INSERT INTO users (id, name) VALUES (2, "Olga"); INSERT INTO users (id, name) VALUES (3, "Elena");

INSERT INTO friends (user_id, friend_id) VALUES (2, 1); INSERT INTO friends (user_id, friend_id) VALUES (3, 1);

[nik4nik]

Sorry, found an explanation of this issue in the "Foreign Keys bug?" #85. But why does the indication "Pragma foreign_keys = on;" do not help in the script? Should only be indicated in the settings: Database ->Settings -> Execute on database connection. I mean when I read instructions for working with the SQLite base, they remind me: "Indicate the pragma ...". And it suddenly does not help.

[little-brother]

Did you forget delete-operator in the script e.g. delete from users where id = 1. I created tables and then run

PRAGMA foreign_keys=on; -- applied only for a current tab
delete from users where id = 1; -- run in the same tab

The friends-table has lost all rows as expected.

[nik4nik]

Спасибо, получилось.