I have the following structure: (Sorry for awkward names, it is because it is a sqlite database for my iPhone app which is not released yet)
CREATE TABLE klb_log ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, log_comment varchar(512) ) CREATE TABLE klb_log_food_maps ( uid integer, did integer, PRIMARY KEY (uid,did), FOREIGN KEY (uid) references klb_log(id) ON DELETE CASCADE, FOREIGN KEY (did) references klb_food(id) ON DELETE CASCADE ) CREATE TABLE klb_food ( id integer, description varchar(255), PRIMARY KEY (id) )
Is there a reason why the row in klb_log_food_maps
is not removed when I delete a row in klb_log
?
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQLite. A foreign key with a cascade delete can only be defined in a CREATE TABLE statement.
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
CASCADE. It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is either deleted or updated when the parent data is deleted or updated.
ON DELETE CASCADE : SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table. ON DELETE SET NULL : SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted.
Foreign key support is not enabled in SQLite by default. You need to enable it manually each time you connect to the database using the pragma:
PRAGMA foreign_keys = ON
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With