SQLite ON DELETE CASCADE constraint not working properly

I'm trying to delete an entry in a parent table but the corresponding entries in the child table are not getting deleted as I expected.
These are the two tables that I created:

CREATE TABLE
    IF NOT EXISTS "pages" (
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "title" TEXT,
        "author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        "creation_date" DATE,
        "publication_date" DATE
    );

CREATE TABLE
    IF NOT EXISTS "blocks"(
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
        "type" TEXT,
        "content" TEXT,
        "position" INTEGER
    );

And this is the query I'm running:

DELETE FROM pages WHERE id = ?

The entry of the pages table gets deleted correctly, but the deletion doesn't propagate. I also added the PRAGMA foreign_keys = ON; instruction, but that didn't solve the problem. Any help would be appreciated.

As the demo below shows there appears to be nothing wrong with what you have shown so, Either

• the PRAGMA foreign_keys = ON; is not working or,
• the bound value is not a valid id or
• you misunderstand what a child/parent is (i.e. the children of pages are the blocks rows)

In regard to the PRAGMA foreign_keys = ON; not working. Consider the following:-

> In order to use foreign key constraints in SQLite, the library must be
> compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER
> defined.
>If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY
> is not, then SQLite behaves as it did prior to version 3.6.19
> (2009-10-14) - foreign key definitions are parsed and may be queried
> using PRAGMA foreign_key_list, but foreign key constraints are not
> enforced. The PRAGMA foreign_keys command is a no-op in this
> configuration. If OMIT_FOREIGN_KEY is defined, then foreign key
> definitions cannot even be parsed (attempting to specify a foreign key
> definition is a syntax error). see https://www.sqlite.org/foreignkeys.html#fk_enable

Working Demo

The following demonstrates the affect of your deletion and could be used as the basis of a test:-

/* Just in case cleanup */
DROP TABLE If EXISTS pages;
DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS users;
/* Turn Foreign Keys on (reflecting the status Results 1, 2 and 3) */
PRAGMA foreign_keys;
PRAGMA foreign_keys = off; /* to show the difference between on and off)*/
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
/* Create the tables */
CREATE TABLE IF NOT EXISTS users (
	id TEXT PRIMARY KEY
	);
CREATE TABLE
    IF NOT EXISTS "pages" (
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "title" TEXT,
        "author" TEXT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
        "creation_date" DATE,
        "publication_date" DATE
    );
CREATE TABLE
    IF NOT EXISTS "blocks"(
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "page_id" INTEGER REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE,
        "type" TEXT,
        "content" TEXT,
        "position" INTEGER
    );
/* Populates the tables with some test data */
INSERT INTO users VALUES ('user1'),('user2'),('user3');
INSERT INTO pages VALUES 
	(1,'page1title','user1',datetime('now'),datetime('1984-01-01'))
	,(2,'page2title','user1',datetime('now','+1 months'),datetime('1992-01-01'))
	,(3,'page3title','user2',datetime('now','+5 weeks'),datetime('1970-01-01'))
	,(4,'page4title','user3',datetime('now','+100 days'),datetime('2010-01-01'))
	,(5,'page5title','user1',datetime('now','+2 years'),datetime('2003-01-01'))
	,(6,'page6title','user3',datetime('now','+2 months','+3 years','+4 days'),datetime('2015-01-01'))
;
INSERT INTO blocks VALUES
	(1,1,'TYPE1','BLAH1',1)
	,(2,1,'TYPE2','BLAH2',2)
	,(3,1,'TYPE2','BLAH3',3)
	,(4,6,'TYPE3','BLAH4',1)
	,(5,6,'TYPE4','BLAH5',2)
	,(6,6,'TYPE1','BLAH6',3)
	,(7,4,'TYPE3','BLAH7',2)
	,(8,4,'TYPE2','BLAH8',2)
;
/* Show the core data that has been inserted - Result 4*/
SELECT * FROM pages 
	JOIN users ON pages.author = users.id
	JOIN blocks ON blocks.page_id = pages.id
;
/* Delete a single page */
DELETE FROM pages WHERE id = 4;
/* Show the data after the deletion - Result 5*/
SELECT * FROM pages 
	JOIN users ON pages.author = users.id
	JOIN blocks ON blocks.page_id = pages.id
;
/* Show the blocks, as blocks with id 7 and 8 should have been deleted through propogation - Result 6*/
SELECT * FROM blocks;
/* Cleanup the test environment */
DROP TABLE If EXISTS pages;
DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS users;

Results

• Result 1 foreign_keys 1

• Result 2 foreign_keys 0

• Result 3 foreign_keys 1

• Result 4 (Initial data):-

• 

• Result 5 (after delete of pages with id 4):-

• 

• Result 6 (all remaining rows from the blocks table):-

• 

  • i.e. the deletion of the pages row with an id of 4 has resulted in the related blocks rows being deleted via propagation