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
 DELETE CASCADE IS NOT WORKING
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IleanaAlvare
Starting Member

Canada
7 Posts

Posted - 02/17/2014 :  22:54:35  Show Profile  Reply with Quote
Hi,
I'm Using SQL Server 2008.

I have three Tables: Customers, CustomerAddress, CustomerPhone. I created the FK constraint as follows:
CREATE TABLE CUSTOMERS (
CustomerID INT NOT NULL PRIMARY KEY IDENTITY,
CustomerFirstName VARCHAR(45) NOT NULL,
CustomerLastName VARCHAR(45) NOT NULL,
CustomerAddressID INT NOT NULL,
CustomerPhoneID INT NOT NULL,

CONSTRAINT fk_Customer_Address
FOREIGN KEY (CustomerAddressID)
REFERENCES CUSTOMERADDRESS(CustomerAddressID)
ON DELETE CASCADE
ON UPDATE NO ACTION,

CONSTRAINT fk_Customer_Phone
FOREIGN KEY (CustomerPhoneID)
REFERENCES CUSTOMERPHONE(CustomerPhoneID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
)

After all INSERT statements have been done. I did the following delete statement.
DELETE FROM CUSTOMERS
WHERE CustomerID=4;
My expectacion was that if I delete a customer record, the corresponding address and phone records would be deleted as well. However, I don't see that, the client record is deleted but not the address and Phone record. Why? What Am I missing here?

Help PLEASEEEEEE

Thanks

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 02/17/2014 :  23:37:15  Show Profile  Reply with Quote
the way you have it modeled you would need to delete a row from customerAddress which would cascade delete your customer.
If you want to delete a customer and have that cascade delete an address you would need to put customerid in customerAddress. with fk on customerAddress.customerid referencing customers.customerid

Be One with the Optimizer
TG
Go to Top of Page

IleanaAlvare
Starting Member

Canada
7 Posts

Posted - 02/18/2014 :  09:11:42  Show Profile  Reply with Quote
Hi TG,

Thanks for your response, I will change the design to the way you said and give ita try to the Delete Statement.

Thanks for your help
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.19 seconds. Powered By: Snitz Forums 2000