RedGate SQL Search - Cannot Find "ON DELETE CASCADE"

• aochss

  SSCommitted

  Points: 1677

  More actions

  November 12, 2013 at 12:56 pm

  #282446

  Today I found myself having to remove records in a part of our database not yet visited my me. To my surprise, the table I removed records from was referenced with a FK that had the "ON UPDATE CASCADE" and "ON DELETE CASCADE" on them causing the child records to be deleted.

  Curious to find out if there were more of these, I turned to SQL Search and noticed that it looks like the search tool ignores the "DELETE CASCADE" key words.

  Found this work around on the DevShed site...

  SELECT C.*

  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

  WHERE DELETE_RULE = 'CASCADE'

  I am not a big fan of CASCADED FK constraints since it hides the fact (in results pane in SSMS) that you are not just deleting on record, but its children as well.

  For example:

  CREATE TABLE ParentTable(

  [ID] [int] NOT NULL,

  [Description] [varchar](50) NOT NULL,

  CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )) ON [PRIMARY]

  GO

  drop table ChildTable

  CREATE TABLE ChildTable(

  [ChildID] [int] NOT NULL IDENTiTY,

  [ParentID] [int] NOT NULL,

  [Description] [varchar] (50) NOT NULL,

  CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED

  (

  [ChildID] ASC

  )) ON [PRIMARY]

  GO

  ALTER TABLE ChildTable WITH CHECK ADD CONSTRAINT [FK_ChildTable_Parent] FOREIGN KEY([ParentID])

  REFERENCES ParentTable ([ID])

  ON DELETE CASCADE

  GO

  -- INSERT Data and Validate

  INSERT INTO ParentTable values(1, 'Mom'), (2, 'Dad')

  INSERT INTO ChildTable (ParentId, Description) values (1, 'Jane Belongs to Mom'), (1, 'Bob Belongs to Mom')

  INSERT INTO ChildTable (ParentId, Description) values (2, 'Ralph Belongs to Dad'), (2, 'Mario Belongs to Dad')

  -- Delete Data - Shows only one record affected.

  begin transaction

  DELETE ParentTable WHERE ID = 1

  -- commit

  -- rollback

  Hope this helps...

  Anton