How to delete records on dependent tables? Thank You.

microsoft.public.sqlserver.programming

24 Mar 2006 2:35 PM

Miguel Dias Moura

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

24 Mar 2006 2:45 PM

Jim Underwood

You 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.

Show quote

"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
>

31 Mar 2006 4:31 PM

Miguel Dias Moura

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

Show quote

"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 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
> >

31 Mar 2006 6:09 PM

Jim Underwood

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

Show quote

"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:
>
> > You 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
> > >
>