Delete word, its meanings, its meaning’s example sentences from DB

deletesqlite

I have three tables as below (simplified for demonstration):

words
=====
integer id
text    word

meanings
========
integer id
integer word_id
text    meaning

examples
========
integer id
integer meaning_id
text    sentence

where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how?

Edit1: I am using SQLite3 as the database.

Edit2: I figured the following solution which requires 3 sql queries in order:

DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id);
DELETE FROM meanings WHERE word_id=the_given_id;
DELETE FROM words WHERE id=the_given_id;

I'm still looking for the answer to my question: is the whole process possible to be done in one query?

Best Answer

Use the cascade deletion. Does SQLite support ON DELETE CASCADE declaration for FOREIGN KEY?

Related Question