1

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?

Improve this question
2
  • Which RDBMS are you using?
    – Philᵀᴹ
    Jan 15, 2013 at 23:42
  • 2
    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, 2013 at 22:39

1 Answer 1

Reset to default
1

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

Improve this answer
5
  • 2
    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, 2013 at 22:38
  • Violation of reference integrity is a poor suggestion.
    – msi77
    Jan 17, 2013 at 13:55
  • 1
    @HLGEM: SQLite doesn't have write concurrency and locks the entire database, so locking doesn't making a difference.
    – CL.
    Jan 17, 2013 at 15:45
  • 1
    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, 2013 at 16:22
  • 2
    @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, 2013 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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