SQL ON DELETE CASCADE

Index, feed.

Let’s create a queries table, and a queries_reports table that holds a list of reports for each record in queries:

-- Used to render tables for this post
.mode markdown

-- SQLite doesn't enforce constraints by default. Enable checking
-- with:
PRAGMA foreign_keys = ON;

Foreign key without ON DELETE¹

CREATE TABLE queries (id TEXT PRIMARY KEY);
CREATE TABLE queries_reports (
  query_id TEXT NOT NULL
    REFERENCES queries,
  report_id TEXT NOT NULL,
  PRIMARY KEY (query_id, report_id)
);

At this point, thanks to REFERENCES query², we are not allowed to insert a record into queries_reports that refers to a non-existent query:

sqlite> INSERT INTO queries_reports (query_id, report_id) VALUES
   ...>   ('query0', 'report0');
Error: FOREIGN KEY constraint failed

Creating records with matching IDs in queries beforehand, on the other hand, works:

INSERT INTO queries (id) VALUES ('query0'), ('query1');

INSERT INTO queries_reports (query_id, report_id) VALUES
   ('query0', 'report0'),
   ('query0', 'report1'),
   ('query1', 'report0'),
   ('query1', 'report1');

sqlite> SELECT * FROM queries;

→

sqlite> SELECT * FROM queries_reports;

→

Deleting a query that has a reference from queries_reports does not work:

sqlite> DELETE FROM queries WHERE id = 'query0';
Error: FOREIGN KEY constraint failed

Deleting referencing rows from queries_reports first lets us delete from queries:

DELETE FROM queries_reports WHERE query_id = 'query0';
DELETE FROM queries WHERE id = 'query0';

sqlite> SELECT * FROM queries;

→

sqlite> SELECT * FROM queries_reports;

→

Notes:

• Preventing insertion of records in queries_reports that have dangling query references is what we wanted; however,

• preventing deletion of records in queries because of existing references in queries_reports may not be what we want: instead, our use case may call for deletions on queries automatically deleting referencing records in queries_reports as well. This is what ON DELETE CASCADE accomplishes.

ON DELETE CASCADE

We alter the definition for table queries_reports as follows to add ON DELETE CASCADE (keeping the same create statement for queries and the same two insert statements):

CREATE TABLE queries_reports (
  query_id TEXT NOT NULL
    REFERENCES queries ON DELETE CASCADE,
  report_id TEXT NOT NULL,
  PRIMARY KEY (query_id, report_id)
);

At this point, the contents of the two tables are:

• queries:

  id
  query0
  query1

• queries_reports:

  query_id	report_id
  query0	report0
  query0	report1
  query1	report0
  query1	report1

Deleting from queries silently deletes matching rows from queries_reports:

sqlite> DELETE FROM queries WHERE id = 'query0';
# Succesful.

Final state of the tables:

• queries:

  id
  query0

• queries_reports:

  query_id	report_id
  query0	report0
  query0	report1

www.kurokatta.org

David Haguenauer <www@kurokatta.org>
$Id: on-delete-cascade.malblog,v 1.2 2026/03/01 04:32:37 homer Exp $