CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32828

How can I delete child rows when I delete a parent row in SQL Server?

I have two tables:

CREATE TABLE [dbo].[AdminTest] (
    [AdminTestId]  INT            IDENTITY (1, 1) NOT NULL,
    [Title]        NVARCHAR (100) NOT NULL,
    CONSTRAINT [PK_AdminTest] PRIMARY KEY CLUSTERED ([AdminTestId] ASC)
);
GO
CREATE NONCLUSTERED INDEX [Test_ExamId_IX]
    ON [dbo].[AdminTest]([ExamId] ASC);
CREATE TABLE [dbo].[AdminTestQuestion] (
    [AdminTestQuestionId] INT              IDENTITY (1, 1) NOT NULL,
    [AdminTestId]         INT              NOT NULL,
    [QuestionUId]         UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_AdminTestQuestion] PRIMARY KEY CLUSTERED ([AdminTestQuestionId] ASC),
    CONSTRAINT [FK_AdminTestQuestionAdminTestId] FOREIGN KEY ([AdminTestId]) REFERENCES [dbo].[AdminTest] ([AdminTestId])
);
GO
CREATE NONCLUSTERED INDEX [AdminTestQuestion_AdminTestId_IX]
    ON [dbo].[AdminTestQuestion]([AdminTestId] ASC);

Is there some way that I can change the definition of the tables so that when I delete a row from AdminTest then all the child rows in AdminTestQuestions for that test are deleted?

Upvotes: 0

Views: 110

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

You can add ON DELETE CASCADE to your foreign key constraint;

CONSTRAINT [FK_AdminTestQuestionAdminTestId] 
  FOREIGN KEY ([AdminTestId]) REFERENCES [dbo].[AdminTest] ([AdminTestId])
  ON DELETE CASCADE

An SQLfiddle to test with.

I may not need to point out that this is not necessarily a good idea in the long run, since it will make implicit changes to your data that other users may not know about. Use with caution.

Upvotes: 2

Related Questions