DELETE CASCADE IS NOT WORKING - 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

DELETE CASCADE IS NOT WORKING

Author

Topic

IleanaAlvare
Starting Member

7 Posts

Posted - 2014-02-17 : 22:54:35

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-17 : 23:37:15

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

IleanaAlvare
Starting Member

7 Posts

Posted - 2014-02-18 : 09:11:42

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources