Foreign Key In SQLite Does Not Work As Expected
Sometimes, writing unit tests that have operations on database are required to clean up tables after each test (or several tests). To garantee the unit tests don't affect each other, having a clean and isolated environment is critical. When I was writing unit tests for my side project, I found that the foreign key cascade funtionality didn't work as expected.
In my SQLite database, I have the following tables created:
1CREATE TABLE users (
2 id INTEGER PRIMARY KEY,
3 username TEXT NOT NULL,
4 password TEXT NOT NULL,
5 created_at INTEGER
6)
7
8CREATE TABLE parameters (
9 user_id INTEGER NOT NULL,
10 secret TEXT NOT NULL,
11 issuer TEXT NOT NULL,
12 account TEXT NOT NULL,
13 FOREIGN KEY (user_id) REFERENCES users(id)
14 ON DELETE CASCADE ON UPDATE CASCADE,
15 PRIMARY KEY (user_id, secret)
16)
After populated some data into these tables, I got:
1db.sqlite> SELECT * FROM users;
2+----+-------------+--------------+------------+
3| id | username | password | created_at |
4+----+-------------+--------------+------------+
5| 1 | John | fE7aG1nX4cT3 | 1665933898 |
6+----+-------------+--------------+------------+
71 row in set
8Time: 0.025s
9
10
11db.sqlite> SELECT * FROM parameters;
12+---------+--------------------+--------+-----------------+
13| user_id | secret | issuer | account |
14+---------+--------------------+--------+-----------------+
15| 1 | 3tffbcdf8523cb5c29 | Google | user@google.com |
16+---------+--------------------+--------+-----------------+
171 row in set
18Time: 0.015s
To delete all the rows in these two tables, we could execute these statements:
1DELETE FROM users;
2DELETE FROM parameters;
The second statement is actually redundant in this case since deleting rows in users table
will also clean up parameters table since parameters table has a foreign key user_id that is referenced to users.id.
So I skipped the second statement and here is what happened:
1db.sqlite> DELETE FROM users;
2You are about to run a destructive command.
3Do you want to proceed? (y/n): y
4Your call!
5Query OK, 1 row affected
6Time: 0.003s
7
8db.sqlite> select * from parameters;
9+---------+--------------------+--------+-----------------+
10| user_id | secret | issuer | account |
11+---------+--------------------+--------+-----------------+
12| 1 | 3tffbcdf8523cb5c29 | Google | user@google.com |
13+---------+--------------------+--------+-----------------+
141 row in set
15Time: 0.014s
Why is that? parameters table is supposed to be empty. Which means there might be something wrong in the foreign key.
After some researches on internet, I can confirm that SQLite does support foreign key constraint.
But, it's disabled by default according to the docs:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)
To check whether it's enabled, we can use PRAGMA foreign_keys to find out:
1db.sqlite> PRAGMA foreign_keys;
2+--------------+
3| foreign_keys |
4+--------------+
5| 0 |
6+--------------+
71 row in set
8Time: 0.014s
So, here we go, we know who the culprit is. Now just execute PRAGMA foreign_keys = ON to enable it.
1db.sqlite> PRAGMA foreign_keys = ON;
2Query OK, 0 rows affected
3Time: 0.001s
Try PRAGMA foreign_keys again to confirm it's enabled:
1db.sqlite> PRAGMA foreign_keys;
2+--------------+
3| foreign_keys |
4+--------------+
5| 1 |
6+--------------+
71 row in set
8Time: 0.013s
Now the foreign key constraints cascade functionality should work as expected.
But one thing to notice, the documentation mentions that it must be enabled for each database connection. Which means this setting is not persistent and we need to enable it every time we connect to SQLite database. All of the pragma statements in SQLite are non-persistent, except WAL journaling mode.