November 12, 2013 at 12:56 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply