How to delete records on dependent tables? Thank You.

Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>