Solved

cascade delete not being applied

Posted on 2007-03-23
3
361 Views
Last Modified: 2011-10-03
Hi

I have a cascade delete rule between the 2 tables and it doesnt seem to be working.

The tables are Customer and aspNet_Users. When i delete a customer row it is supposed to delete corresponding row in the aspnet users table. The customer is delted but the user is remaining. My table defintiion is below. The deletion from teh customer table is occuring programmatically but i'm not getting an any errors in my program. Any ideas what i can do?

USE [Courier]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 03/23/2007 18:17:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
      [CustomerID] [uniqueidentifier] NOT NULL,
      [CustomerTypeID] [int] NULL,
      [TitleID] [int] NULL,
      [Forename] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [Surname] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [Add1] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [Add2] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [Town] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [City] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [County] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
      [Postcode] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
      [Telephone] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
      [Mobile] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
      [Email] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
      [Fax] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
      [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_aspnet_Users] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_aspnet_Users]
GO
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_CustomerType] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerType] ([CustomerTypeID])
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_CustomerType]
GO
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Title] FOREIGN KEY([TitleID])
REFERENCES [dbo].[Title] ([TitleID])
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Title]
0
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 18782198
Cascade delete works backwards from how you are thinking. As it is set up, aspnet_users is the parent and customer is the child.  So, if you delete an aspnet_user row, the customerids in customer that match userids in aspnet_users get deleted. It doesn't work the other way around.

0
 
LVL 42

Expert Comment

by:dqmq
ID: 18782263
The business rule implied by your foreign key constraint is that an aspnet_user may have multiple customers.  So, it doesn't make sense to delete one customer and expect the aspnet_user to get deleted also. That would orphan other customers of that aspnet_user.

If aspnet_user is a subset of customers (or even one-to-one) with customers, then you can put the foreign key on the aspnet_user table and the cascade delete will work.  The business rule implied by that arrangement is that a customer may have multiple aspnet_users. (Though it still works if there are zero or one aspnet_users for the customer).  Then, when you delete the customer all of it's aspnet_users get deleted, too.  Without cascade delete, the customer delete would be prevented as long as aspnet_users are present for that customer.  
0
 

Author Comment

by:andieje
ID: 18785300
hi dgmg

there is actually a one to one relationship between users and customers so it doesnt matter which is the parent and which is the child in this case. I'm essentially trying to add some extra properties onto a table which already exists that I cant modify by creating the customer table

so i've got confused havent i and added the cascase in the wrong table. It should be in the Users table not the customer table

thanks
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 5 43
Regarding Disk IO 3 42
Need help with another query 10 36
SQL parsing XML works but want to do it another way 4 18
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question