SQLite Forum

'ON DELETE CASCADE' seems to not be working
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.