1

Sorry for the vague title, I'm not really sure how to define what I am trying to do.

I have 3 tables: categories - has a unique ID (cat_id) questions - has a unique ID and a category foreign key (ques_category_fk) answers - has a unique ID and a questions foreign key (ans_question_fk)

What I am trying to do is delete all the questions and answers under a single category. Deleting the questions is no problem. However, I'm not sure how to delete the answers using the same SQL statement.

I thought of loading all the questions, iterating through them or adding a category foreign key to the answers. Both of these seem inefficient.

Here is my current delete statement:

DELETE FROM questions WHERE ques_category_fk = @fk
Lee Loftiss
  • 3,035
  • 7
  • 45
  • 73

2 Answers2

1

Much easier to write and maintain solution will be run those queries, deletion of answers and question, separately in one transaction, and then commit it. This way you will be sure that either they are all deleted (or none if query failed), and you don't have to resort to long and confusing queries.

To put it in a bit of mock example, lets say that you want to remove a question, and subsequent answers, with id=6 (in my example idquestion is pkey in table questions, and is the name of f_key in table answers):

start transaction;
delete from answers where idquestions = 6;
delete from questions where idquestions = 6;
commit;

And then if you want to remove many question, first run a query to select all question ids that you want to remove and iterate over them - running the above set of queries for each id.

Tymoteusz Paul
  • 2,732
  • 17
  • 20
  • Sorry, I'm not clear on what you are saying. Are you saying I should use my idea above of iterating through the questions and deleting the answers for each question? – Lee Loftiss Nov 02 '14 at 23:37
  • Pretty much, but with a but. And the but is - run each deletion set *of one question and answers to it) as one transaction. This way if, for some reason, removing answers, or question, will fail you won't be left with a mess. – Tymoteusz Paul Nov 02 '14 at 23:38
  • So you're saying to load all the questions, then for each question load all the answers, then delete the answers one at a time? – Lee Loftiss Nov 02 '14 at 23:41
  • Thanks Puciek. I want you to know, though, I accepted your answer because it looks clearer to me, not because of the challenging statement you made in Dwane Towell's answer. I think that was unprofessional and you should let your answers speak for themselves. – Lee Loftiss Nov 05 '14 at 01:01
  • @LeeLoftiss This is exactly why stack overflow comes with a comment field, so we can challenge an answer in more meaningful way than just down voting, which I avoid and didn't use. And the performance impact of his solution is quite severe when it will come to scaling application vertically, so it is well worth to make that notion clear to everyone. – Tymoteusz Paul Nov 05 '14 at 01:06
0

Create the "child" table to cascade the delete from the referenced "parent" table. Something like this:

CREATE TABLE answers (
    id SERIAL,
    question_id int REFERENCES questions(id) ON DELETE CASCADE,
    ...
);

Now, deleting the question records will also delete the referenced answer records.

Dwayne Towell
  • 8,154
  • 4
  • 36
  • 49
  • I created the tables using the Database Explorer in Visual Studios Express. Is possible to set this through the Database Explorer? – Lee Loftiss Nov 03 '14 at 00:51
  • @LeeLoftiss This sort of references is nice but doesn't scale up well (either vertically or horizontally) and as, hopefully, user base of your application will grow, this will become a major problem. And ask yourself - is this potential time-bomb worth the laziness of sending one query instead of two? – Tymoteusz Paul Nov 03 '14 at 01:42
  • Huh? If the "sub" record "belong" to the "parent" record, then why would you do it as two operations--that seems more likely to be a future error than recording what to do to maintain referential integrity. – Dwayne Towell Nov 04 '14 at 01:44
  • @DwayneTowell that is why you should wrap them within a one transaction, this way in case of any trouble on either side, no changes will be made and integrity between data will remain. And it's worth doing in the more "manual" way because of the performance expense of not only having an actual f_key in your table, but then also having constraints that have to be checked. This cost isn't minuscule, and when those tables will hold TB+ worth of records (very likely sooner than that) the impact of this solution will be close to crippling. – Tymoteusz Paul Nov 05 '14 at 01:05