June 16, 2010 at 6:41 am
Hello you all , I'm newbie to SQL Server .
I have a table like this :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Codings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NOT NULL,
[CodeId] [int] NOT NULL,
[Title] [nvarchar](100) COLLATE Arabic_CI_AI NOT NULL,
CONSTRAINT [PK_Codings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
and i Have data like this :
Id ParentId CodeId Title
----------- ----------- ----------- ------------------
1 0 0 Educational Level
3 1 1 BS
5 1 2 Ms
6 1 3 PHD
7 0 0 Gender
8 7 1 Male
9 7 2 Female
Now i want , when i delete a record with Id = 7 ( or any record that Parent id = 0 ) , All record that their Parent Id = 7 , delete automatically .
I know that's possible via triggers , but i suppose there would be more simple way to do this ( like relation ) .
could you help me at this ?
Thank you
June 16, 2010 at 7:06 am
The foreign key constraint can do it for you. Look up "FOREIGN KEY" and "ON DELETE CASCADE" in BOL.
-- Gianluca Sartori
June 16, 2010 at 8:34 am
Agreed. Research "self-referencing tables"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply