Delete cascade syntax in sql server

• raosankar19

  Old Hand

  Points: 309

  More actions

  May 21, 2014 at 10:44 pm

  #308367

  Delete cascade syntax in sql server...

  I want to know delete cascade syntax in sql server...

  🙂

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  May 22, 2014 at 2:01 am

  #1715586

  raosankar19 (5/21/2014)

  ---

  Delete cascade syntax in sql server...

  I want to know delete cascade syntax in sql server...

  🙂

  ON DELETE CASCADE

  😎

  USE tempdb;

  GO

  CREATE TABLE dbo.TBL_PARENT

  (

  PARENT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

  ,PARENT_NAME NVARCHAR(50) NOT NULL CONSTRAINT UNIQUE_PARENT_NAME UNIQUE

  );

  CREATE TABLE dbo.TBL_CHILD

  (

  CHILD_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

  ,PARENT_ID INT NOT NULL CONSTRAINT FK_CHILD_PARENT_ID FOREIGN KEY REFERENCES dbo.TBL_PARENT(PARENT_ID) ON DELETE CASCADE

  ,CHILD_NAME NVARCHAR(50) NOT NULL

  );

  INSERT INTO dbo.TBL_PARENT (PARENT_NAME)

  VALUES (N'P 1'),(N'P 2');

  INSERT INTO dbo.TBL_CHILD (CHILD_NAME,PARENT_ID)

  SELECT N'CHILD 1' AS CHILD_NAME

  ,PARENT_ID

  FROM dbo.TBL_PARENT WHERE PARENT_NAME = N'P 1'

  SELECT * FROM dbo.TBL_CHILD

  DELETE FROM dbo.TBL_PARENT WHERE PARENT_NAME = N'P 1'

  SELECT * FROM dbo.TBL_CHILD

  DROP TABLE dbo.TBL_CHILD

  DROP TABLE dbo.TBL_PARENT