'ON DELETE CASCADE' seems to not be working
(1) By Judge (DerJudge) on 2020-12-18 09:07:45 [link]
Hi everyone, I'm new to SQLite; I am using it in my Python scripts as `sqlite3` build-in module. I have created a minimal DB schema, which has 3 tables: 1. Players 2. Groups 3. Group Assignments The SQL looks like this: ```sql CREATE TABLE IF NOT EXISTS game_group ( group_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL, name text UNIQUE NOT NULL, created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS players ( player_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL, name text UNIQUE NOT NULL, created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS game_group_members ( added_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, group_id integer, player_id integer, PRIMARY KEY (group_id, player_id), CONSTRAINT fk_game_group FOREIGN KEY (group_id) REFERENCES game_group(group_id) ON DELETE CASCADE, CONSTRAINT fk_players FOREIGN KEY (player_id) REFERENCES players(player_id) ON DELETE CASCADE ); ``` I am creating a new database and load this script into it like this: ```python import sqlite3 conn = sqlite3.connect('test_db.sqlite') cur = conn.cursor() cur.executescript(init_sql) ``` `init_sql` contains the previously shown SQL code. I then create a player named `TestUser`: ```python query = "INSERT INTO players (name) VALUES (?)" cur.execute(query, ('TestUser',)) ``` After that, I create a group named `TestGroup` ```python query = "INSERT INTO game_group (name) VALUES (?)" cur.execute(query, ('TestGroup',)) ``` The result looks like this: ```python query = "SELECT * FROM players" cur.execute(query).fetchall() -> [(1, 'TestUser', '2020-12-18 08:35:09')] query = "SELECT * FROM game_group" cur.execute(query).fetchall() -> [(1, 'TestGroup', '2020-12-18 08:42:43')] ``` Next, I assign the player to that group ```python query = "INSERT INTO game_group_members (group_id, player_id) VALUES (?, ?)" cur.execute(query, (1, 1)) query = "SELECT * FROM game_group_members" cur.execute(query).fetchall() -> [('2020-12-18 08:48:15', 1, 1)] ``` My expectation now is that when deleting player '1' from table 'players', the group assignment for player '1' in 'game_group_members' should be deleted as well, due to the 'ON DELETE CASCADE' constraint. ```python query = "DELETE FROM players WHERE player_id = ?" cur.execute(query, (1,)) conn.commit() query = "SELECT * FROM players" cur.execute(query).fetchall() ->  ``` As expected, the table `players` is now empty. But the entry in `game_group_members` still exists: ```python query = "SELECT * FROM game_group_members" cur.execute(query).fetchall() -> [('2020-12-18 08:48:15', 1, 1)] ``` What am I doing or understanding wrong here, please? Thanks for your help! Best regards, Judge
(2) By Stephan Beal (stephan) on 2020-12-18 10:00:49 in reply to 1 [link]
> My expectation now is that when deleting player '1' from table 'players', the group assignment for player '1' in 'game_group_members' should be deleted as well, due to the 'ON DELETE CASCADE' constraint. The *enforcement* of foreign keys is *off* by default (IIRC, for historical compatibility reasons). It can be turned on, however: <https://sqlite.org/pragma.html#pragma_foreign_keys>
(3) By Keith Medcalf (kmedcalf) on 2020-12-18 14:55:09 in reply to 1 [link]
Also, declaring something both UNIQUE and a PRIMARY KEY is a doubly redundant redundancy. Of course the primary key is unique (primary key, except for INTEGER PRIMARY KEY) is merely syntactic sugar (an alternate spelling) of unique. So if you specify UNIQUE PRIMARY KEY you are really declaring something as UNIQUE UNIQUE which conveys no benefit whatsoever. In fact it may be detrimental because some versions of SQLite may take you at your word and create extra useless indexes which will bugger up the works. The current version has been fixed so that it will ignore such common "user bad habits" (like using LEFT JOIN for everything when they mean INNER JOIN) however (a) you are using SQLite3 provisioned by a third-party and they are probably distributing a version that is older than my dead grandma's knickers; and, (b) if you do not fall into the practice of bad habits then the world will not have to take extra precautions to protect itself from you. I would note that even you yourself are internally inconsistent in this regard as in the table game_group_members you declared primary key (group_id, player_id) but failed to also declare that to be unique. So for some reason you failed to be redundantly redundant in that declaration -- one wonders why you did not notice this yourself? See <https://sqlite.org/rowidtable.html> for some interesting facts about rowid tables.
(4) By jose isaias cabrera (jicman) on 2020-12-18 15:26:09 in reply to 3 [link]
>is a doubly redundant redundancy... man, that is funny. A classic Keith.
(5) By Larry Brasfield (LarryBrasfield) on 2020-12-18 16:05:58 in reply to 3 [link]
You have described, with some redundancy, why 'UNIQUE PRIMARY KEY' is redundant, or possibly, with surplusage, singly redundant. But I cannot see that it is either doubly redundant or a redundant redundancy, let alone a doubly redundant redundancy. However, I have to grant bonus points for well demonstrating pleonasm.
(6.1) By Keith Medcalf (kmedcalf) on 2020-12-18 16:18:38 edited from 6.0 in reply to 5 [link]
The word of the day is **pleonasm**, the determination of the precise meaning required resort to a lexicon of the Kings' English (I did not know this word and could not parse it from its roots). Well done -- one should learn something new every day! Thank-you Larry.
(7) By Keith Medcalf (kmedcalf) on 2020-12-18 16:29:10 in reply to 3 [link]
By the way, do not take this as a knock on your endeavours to utilize SQLite3. If you are starting using the documentation in the Python wrapper, it is rather sparse since it is really only documenting the wrapper, and not SQLite itself. SQLite has, like everything else, its own implementation peculiarities. You have come to the right place, however, to find out more about the underlying SQLite RDBMS product. You may wish to also take a look at APSW which is yet Another Python SQLite Wrapper that is intended to expose those things unique to SQLite, and to work as closely as possible to how SQLite works, rather than provide a minimum common denominator db-api interface. <https://github.com/rogerbinns/apsw>
(8) By tom (younique) on 2020-12-19 04:47:09 in reply to 3 [link]
> it will ignore such common "user bad habits" (like using LEFT JOIN for everything when they mean INNER JOIN What do you mean with that? Will SQLite rewrite a LEFT JOIN into an INNER JOIN? 😲
(9) By tom (younique) on 2020-12-19 04:49:19 in reply to 3 [link]
> declaring something both UNIQUE and a PRIMARY KEY is a doubly redundant redundancy So is declaring PRIMARY KEY NOT NULL, but nevertheless you have to do it ;) (I know it's a bug, but you still have to do it.)
(10) By Keith Medcalf (kmedcalf) on 2020-12-19 06:54:28 in reply to 8 [link]
Yes. SQLite will automatically upgrade superfluous outer joins to inner joins.
(11) By L Carl (lcarlp) on 2020-12-19 14:28:48 in reply to 10 [link]
How are “superfluous” outer joins identified?
(12) By Holger J (holgerj) on 2020-12-19 18:44:03 in reply to 9 [link]
Not in SQLite, but in DB2, PRIMARY KEY without NOT NULL produces an error telling you to add NOT NULL.
(13) By Keith Medcalf (kmedcalf) on 2020-12-19 19:31:22 in reply to 11 [link]
Identification is based on the fact that candidate rows resulting from the fixed traversal requirement of the LHS table to the RHS table will subsequently eliminate the outer candidate results (where all the RHS columns are null) because at least one column of the RHS table is constrained not null. Simple example: `select * from t1 left join t2 on t1.a == t2.a where t2.b == 4;` since t2.b cannot be null in the projection result the outer join is really a misspoken inner join and there is not longer a descent order constraint between t1 and t2, and the query planner is free to optimize the query as if `select * from t1 join t2 on t1.a == t2.a where t2.b == 4;` had been given. Similarly for: `select * from t1 left join t2 on t1.a == t2.b join t3 on t3.b == t2.b;` where the results from the outer join can never affect the result because t2.b is constrained not null and therefore the outer join is really an inner join.
(14) By tom (younique) on 2020-12-19 20:02:55 in reply to 12 [link]
Maybe, but it doesn't make any sense because PRIMARY KEY === UNIQUE NOT NULL
(15) By Keith Medcalf (kmedcalf) on 2020-12-20 07:18:28 in reply to 14 [link]
Only for without rowid tables. For rowid tables PRIMARY KEY is merely syntactic sugar for UNIQUE -- except for INTEGER PRIMARY KEY which declares an explicit name for the rowid.
(16) By Keith Medcalf (kmedcalf) on 2020-12-20 07:21:36 in reply to 12 [link]
Technically this is correct. PRIMARY KEY columns must be NOT NULL. Except in SQLite rowid tables where PRIMARY KEY is merely syntactic sugar for UNIQUE and the actual primary key is the rowid.
(17) By Holger J (holgerj) on 2020-12-20 15:48:41 in reply to 14 [link]
Regarding the table itself it may be synonymous, but when referencing a table with ''referencing tablename'' it will always and only refer to the primary key. Referencing anything only ''unique'' requires ''refererencing tablename(col1,col1)''.
(18) By anonymous on 2021-03-20 18:15:17 in reply to 2 [link]
As a developer which used SQLite in various projects it is exactly this opinion which I do not share. First of all I understand your concerns about breaking existing DBs. Following the page <https://sqlite.org/foreignkeys.html> foreign key checking was added roughly 12 years ago. And still in 2021 you continue to force users in each new release to explicitly enable this functionality. This to keep compatibility for DBs which had been around before 2009? User expectations should not be neglected with no valid reason. I guess that 12 years are a long time for fixing the broken DB designs. And as a valid alternative, developers are still free to *disable* the FK checking on a per DB basis. Matthias Wallnöfer PS The same considerations apply also to the nullable PK issue, another long-standing misbehaviour in SQLite.
(19.1) By Keith Medcalf (kmedcalf) on 2021-03-20 18:52:48 edited from 19.0 in reply to 18
You can change the default for foreign key enforcement when you compile SQLite3. Unless you change the default at compile time, the default default is disabled. Perhaps there should be a compile-time option for whether or not NULL is permitted in the declared PRIMARY KEY of rowid tables as well. Similarly, the default default could be to allow NULL in rowid table PRIMARY KEY as is the current case, and if you wish different behaviour then you can change it. Other misfeatures already have this behaviour (foreign-key enforcement, whether to allow double-quoted strings, to name a couple). While DQS works for DML, the options to prevent DQS in DDL does not.