Need help with ALTER On Delete Cascade

[comicrage]

Hi,

I got 3 tables which one has two foreign keys from the other two. Here is a short list:

Category
PK : CategoryID

CategoryLog
PK: CategoryLogID
FK: Category ID
FK: LogID

Log
PK: LogID

I used the following ALTER ... On Delete Cascade statement.

ALTER TABLE CategoryLog
ADD CONSTRAINT fk_Category
FOREIGN KEY (CategoryID)
REFERENCES Category (CategoryID) ON DELETE CASCADE
GO

ALTER TABLE CategoryLog
ADD CONSTRAINT fk_Log
FOREIGN KEY (LogID)
REFERENCES Log (LogID) ON DELETE CASCADE
GO


What I want to do is remove a record from the Log table and it will automatically remove the CategoryLog record with the same LogID value and the record from the Category table with the same CategoryID value.

I used following DELETE statement in the stored procedure to remove old Log records, which will remove the CategoryLog records with the same LogID values. However, nothing is removed from the Category table with the same CategoryID records as in the CategoryLog table which has a relationship with each other.

DELETE FROM Log WHERE DateDiff("d",TimeStamp, getdate()) < 90


Basically, what I try to do is a cascade effect where deleting Log records will results in removing the corresponding CategoryLog and Category table records. Right now, nothing is being removed from the Category table.

Any help is greatly appreciated.