SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cascade delete		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnpapa
Starting Member

7 Posts
Posted - 12/15/2012 :  09:44:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/15/2012 :  10:00:04  Show Profile  Reply with Quote
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
Go to Top of Page

johnpapa
Starting Member

7 Posts
Posted - 12/15/2012 :  10:07:56  Show Profile  Reply with Quote
Please excuse my ignorance. How do I script out the FK?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/15/2012 :  12:05:07  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 12/15/2012 :  12:31:47  Show Profile  Reply with Quote
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/
Go to Top of Page

johnpapa
Starting Member

7 Posts
Posted - 12/16/2012 :  08:29:02  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/16/2012 :  09:22:03  Show Profile  Reply with Quote
That is script for Primary Key. Script out Foreign key. If not use above to create it.
Go to Top of Page

johnpapa
Starting Member

7 Posts
Posted - 12/17/2012 :  02:15:58  Show Profile  Reply with Quote
I recreated the tables and it now works. It appears that the FK was not created in the first place
Thanks,
John
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000