Foreign Key problerm

Results 1 to 4 of 4

Thread: Foreign Key problerm

  1. #1
    Manpeet Guest

    Default Foreign Key problerm

    Hi,<BR>i am using SQL Server 6.5. Now i have 2 tables "answers" and "questions". Now for every question there exist more than 1 answers. so "question_id" which is the primary key in questions table, is(or is intended to be!!) the foreign key in the answers table. so one could expect the following for question_id = 1 in the answers table.<BR><BR>answer_id question_id<BR>--------------------------<BR>1 1<BR>2 1<BR>3 1<BR><BR>Now what i want is that when 1 question is deleted from the "questions" table all it&#039s child answers shuld get deleted from the answers table also.<BR><BR>Please advise with refrence to SQL Server 6.5 (if possible)<BR><BR>thanx<BR><BR><BR> <BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Foreign Key problerm

    You can turn on cascade delete (an option when you set up the foreign key). This means, whenever you delete a primary key, delete all of its associated foreign keys.<BR><BR>The better way to do it, in my opinion, is to code it in your ASP. So, when they say: delete question id X, first you do:<BR><BR>DELETE FROM Answers WHERE Question_ID = X<BR><BR>to remove all of the answers, then you do:<BR><BR>DELETE FROM Questions WHERE Question_ID = X<BR><BR>Finally, you can create a delete trigger in the Questions table that will cascade delete the questions in the answer table. I can provide code for this if you like... I&#039d suggest doing it the ASP way, though.

  3. #3
    Manpreet Guest

    Default RE: Foreign Key problerm

    I have not written an interface for the deletion thro ASP(though writng one wont be a pain). However, the idea of a trigger is cool. if u could help me with that , i guess it would be great.<BR><BR>what say?<BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    15

    Default RE: Foreign Key problerm

    sd


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •