Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ON DELETE CASCADE in sqlite3

Tags:

sql

sqlite

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?

like image 727
LuckyLuke Avatar asked May 04 '11 21:05

LuckyLuke


People also ask

Does SQLite support on delete cascade?

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.

Is on delete cascade?

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.

Does On Update Cascade also delete?

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.

What is on delete cascade and on delete set NULL?

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.


1 Answers

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 
like image 101
Paul Lefebvre Avatar answered Sep 24 '22 13:09

Paul Lefebvre