Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

integer id
text    word

integer id
integer word_id
text    meaning

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?

share|improve this question
Which RDBMS are you using? – Phil Jan 15 '13 at 23:42
What you have done is the best method. You cannot delete from mulitple tables in one statement in any database I know of and cascade delete is too dangerous to use in production. – HLGEM Jan 16 '13 at 22:39

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

share|improve this answer
CAScade delete is a poor suggestion. It shoudl not be used on many production databases as it can lock up several tables while delting millions of child records. It is far better to write individual delete statments (and have the ability to loop through set of records if the child table will have many entries). – HLGEM Jan 16 '13 at 22:38
Violation of reference integrity is a poor suggestion. – msi77 Jan 17 '13 at 13:55
@HLGEM: SQLite doesn't have write concurrency and locks the entire database, so locking doesn't making a difference. – CL. Jan 17 '13 at 15:45
So locking the entire database while you delete millions of child records is a good thing? There is no circumstance in which I would allow cascade delete be put on a production database and most professional dbas I know agree with me. – HLGEM Jan 17 '13 at 16:22
@msi77: Not having cascading deletes in no way implies loss of referential integrity in any DB worth using (and many not worth using to!) - you will simply get an FK constraint error in circumstances were it would be effective rather than it letting you carry on and violate the foreign key relationship. I too tend to avoid cascaded deletes like the plague. – David Spillett Jul 16 '13 at 0:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.