Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
    
Which RDBMS are you using? –  FreshPhilOfSO Jan 15 '13 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 '13 at 22:39
add comment

1 Answer

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

share|improve this answer
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 '13 at 22:38
    
Violation of reference integrity is a poor suggestion. –  msi77 Jan 17 '13 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 '13 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 '13 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 '13 at 0:22
add comment

Your Answer

 
discard

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.