by c3mnr0nz0yfgvxg4gi0sxhnwl9 (on Github)
created Sunday, 2026 January 11
updated Thursday, 2026 January 15
original at cybertiggyr.com/q54ral3.html
Under construction!!!
(short answer) I did a test to see how much
REFERENCES constraints, in which a table refers to another
table, affect the performance of
DELETE FROM.
(longer answer) I understood that REFERENCES constraints between tables in a relational database affect the time required for DELETE FROM. I wanted to see the effect for myself, so I ran a simple experiment.
Here is an explanation of the experiment & the results. The source code for the experiment is q54ral3 on Github.
I used SQLite3 for the test. I used “PRAGMA foreign_keys
= ON” to tell SQLite3 to enforce REFERENCES
constraints. Some of my tables had no such constraints. Some had 1
constraint. Some had 10 such constraints.
Deleting about 18,181 rows from a table of 200,000 rows when no other table uses a REFERENCES constraint on the 200,000-row table required 0 seconds.
In a similar situation but with another table having a single REFERENCES constraint on the 200,000-row table required 31 seconds.
In a similar situation but with another table having 10 REFERENCES constraints on the 200,000-row table required 397 seconds.
I created 3 pairs of tables.
Each pair contains a Base table & a Dep(endent) table.
The Base tables always have an integral primary key, an integer (that we don't use), & a text field (that we don't use).
Each Dep table has an integral primary key & ten more columns that contain primary keys from the corresponding Base table.
The “0” pair has no REFERENCES constraints at all.
In other words, 10 columns in each row of the Dep0
table refer to rows in the Base0 table, but we don't
tell the databaes about that; we don't use a REFERENCES at all
when we create the Dep0 table.
The “1” pair has exactly 1 REFERENCES constraint.
The Dep1 table is like the Dep0
table except that exactly 1 of its columns is created with a
“REFERENCES Base1(base_id)” constraint.
The “10” pair has 10 REFERENCES constraints.
The Dep10 table is like the Dep0
table except that 10 of its columns are created with
“REFERENCES Base1(base_id)” constraint.
(Sometimes I hate English descriptions. This is one of those times. See the code on Github. Search for “CREATE TABLE”.)
We first stuff 200,000 rows into the 3 base tables:
Base0, Base1, & Base10.
We know that their base_id columns will be
1..200000 for each table. There are 2 other columns on each
table (an integer & a TEXT), that we populate but don't use.
Then we insert rows into all 3 Dep tables. We take
advantage of the knowledge that the ids of the Base
rows are on 1..200000. It's unrealistic for an application, but
it mades it possible to ensure later that we'll delete rows that
aren't referenced by the Dep tables.
For each group of 10 rows in a Base table, we refer to all 10 of those from a row in the corresponding Dep table. Then we skip the 11th row (so we can delete it later). Then the next row in the Dep table referes to the next 10 rows in the Base table.
Once the database is setup, we delete every 11th row from each Base table; we note the time required to do it. Remember that the Dep tables refere to 10 rows from their Base tables, then skip the 11th row, then repeat. So every 11th row can be deleted without error.
My motivation was curiosity, not a problem that needed solving.
Nevertheless, if you have a performance problem with
DELETE FROM & you suspect that REFERENCES
constraints might be the cause, read on.
On SQLite3, if you want it to check REFERENCES constraints, you must
send it “PRAGMA foreign_keys = ON” soon after
connecting to it. If you don't send it that pragma, it won't check
the REFERENCES constraints & you won't pay the cost for
them during DELETE FROM. (I did not check this, but it's
what the documentation says.)
Other databases might have their own way of disabling
REFERENECES constraint checks. Or you might be able to
remove the REFERENCES constraint from a table before DELETE FROM a table
that's the target of those REFERENCES. I did not try these.