Cascade delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cascade delete

Author

Topic

johnpapa
Starting Member

7 Posts

Posted - 2012-12-15 : 09:44:40

I am having problems with cascade delete of the child records (they do not get deletd). Assume tblClient with field lngID (PK) and tblClientItem with field lngClientItem (FK). I used a Database Diagram and created a FK with these two fields. I also set the Delete Rule to Cascade. When I delete a specific record in lngID the corresponding lngClientItem records do not get deleted.

Any ideas.
John

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-15 : 10:00:04

Script out the FK and check if it is correct.

I should like this

ALTER TABLE [dbo].[tblClientItem]  WITH CHECK 
ADD CONSTRAINT [FK_tblClientItem_tblClient] FOREIGN KEY([lngClientItem])
REFERENCES [dbo].[tblClient] ([IngID])
ON DELETE CASCADE

johnpapa
Starting Member

7 Posts

Posted - 2012-12-15 : 10:07:56

Please excuse my ignorance. How do I script out the FK?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-15 : 12:05:07

In SSMS Object Explorer, and find the table name. It will be under the Databasename -> Tables. Expand the table node and find the Keys node. Right click on the keys and select Script Keyas -> Create To -> New Query Window. Alternatively, you can right click on the table name and do the same thing - it will script the table along with keys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-15 : 12:31:47

I really prefer implementing trigger/procedure based logic over cascading deletes. The reason is unless proper documented cascading deletes can be a real pain in maintaining moving forward. Imagine case of someone inheriting DB without having any documentation on cascading deletes. As there's no direct accountability they wont be aware of impact caused due to cascadng deletes especially in cases where there's no auditing/logging

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnpapa
Starting Member

7 Posts

Posted - 2012-12-16 : 08:29:02

sunitabeck,

This is what I get. Any ideas why it does not cascase delete? I do not see mention of tblClientItem.

USE [JP1]
GO

/****** Object:  Index [PK_tblClient]    Script Date: 16/12/2012 3:23:48 µµ ******/
ALTER TABLE [dbo].[tblClient] ADD  CONSTRAINT [PK_tblClient] PRIMARY KEY CLUSTERED 
(
	[lngID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

visakh16, regarding the use of triggers/procedures it is probably a more proper way to go, but was curious to see the reason why the cascade delete did not work.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-16 : 09:22:03

That is script for Primary Key. Script out Foreign key. If not use above to create it.

johnpapa
Starting Member

7 Posts

Posted - 2012-12-17 : 02:15:58

I recreated the tables and it now works. It appears that the FK was not created in the first place
Thanks,
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources