CODEWITHSUNDEEP
sql-serversql-server-2012
user1943020
user1943020

Reputation:

How can I make child rows delete when I delete a parent row?

I have the following DDL:

CREATE TABLE [dbo].[Test] (
    [UserId]                NVARCHAR (128) NOT NULL,
    [TestId]                INT            IDENTITY (1, 1) NOT NULL,
    [ExamId]                INT            NOT NULL,
    [Title]                 NVARCHAR (100) NULL,
    [Status]                INT            NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC),
    CONSTRAINT [FK_TestExam] FOREIGN KEY ([ExamId]) REFERENCES [dbo].[Exam] ([ExamId])
);


CREATE TABLE [dbo].[TestQuestion] (
    [TestQuestionId] INT           IDENTITY (1, 1) NOT NULL,
    [TestId]         INT           NOT NULL,
    [ThisAnswered]   BIT           DEFAULT ((0)) NULL,
    CONSTRAINT [PK_TestQuestion] PRIMARY KEY CLUSTERED ([TestQuestionId] ASC),
    CONSTRAINT [FK_TestQuestion_Test] FOREIGN KEY ([TestId]) REFERENCES [dbo].[Test] ([TestId])
);


GO
CREATE NONCLUSTERED INDEX [TestQuestionTest_TestId_IX]
    ON [dbo].[TestQuestion]([TestId] ASC);

I tried to delete the parent Test but it gives me an error. Is there a way that I can modify this so it automatically deletes all the records in TestQuestion table when I delete the test from the Test table?

Upvotes: 0

Views: 47

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

you just need a cascade delete

CONSTRAINT [FK_TestQuestion_Test] FOREIGN KEY ([TestId]) REFERENCES [dbo].[Test] ([TestId]) 
ON DELETE CASCADE

EDIT

ALTER TABLE [dbo].[TestQuestion] drop constraint [FK_TestQuestion_Test];

ALTER TABLE [dbo].[TestQuestion]
ADD CONSTRAINT [FK_TestQuestion_Test] FOREIGN KEY ([TestId]) REFERENCES [dbo].[Test] ([TestId]) ON DELETE CASCADE;

see SqlFiddle

Upvotes: 4

Related Questions