cascade delete not being applied

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]
andiejeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
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
andiejeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.