A table that has a relation to itself

  • 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

  • The foreign key constraint can do it for you. Look up "FOREIGN KEY" and "ON DELETE CASCADE" in BOL.

    -- Gianluca Sartori

  • 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