sql delete cascade not working

Question

I have a Microsoft SQL Database with 2 tables: dog and cat.

"dog" table has a primary key column called "food", which is related to a column called "food" as well in the "cat" table, which acts as the foreign key.

The relationship between the tables has an "on delete cascade" rule set, so when I delete a row from "dog" table, the relveant rows from "cat" table should be deleted as well.

But the rows in "cat" table do net get deleted, they stay. I use the Microsoft SQL Database manager to delete the row in "dog" table.

Any idea why this happens? do I need to use a special delete sql command to delete a row in this manner?

//edit

the script for the tables is:

USE [VELES]
GO
/****** Object:  Table [dbo].[Periods]    Script Date: 01/18/2011 14:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Periods](
    [PeriodID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PeriodName] [nvarchar](50) COLLATE Hebrew_CS_AS NULL,
    [PeriodStartDate] [smalldatetime] NOT NULL,
    [PeriodEndDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Periods] PRIMARY KEY CLUSTERED 
(
    [PeriodID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


USE [VELES]
GO
/****** Object:  Table [dbo].[Exams]    Script Date: 01/18/2011 14:55:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Exams](
    [ExamID] [int] IDENTITY(1,1) NOT NULL,
    [ExamUserID] [char](7) COLLATE Hebrew_CS_AS NOT NULL,
    [ExamBase] [tinyint] NOT NULL,
    [ExamUserTimesAccessed] [tinyint] NULL,
    [ExamMaxTimesToOpen] [tinyint] NOT NULL,
    [ExamUserLastTimeOpened] [datetime] NULL,
    [ExamUserLastTimeFinished] [datetime] NULL,
    [ExamTimeToFinish] [int] NOT NULL,
    [ExamPassGrade] [int] NOT NULL,
    [ExamPeriod] [int] NOT NULL,
    [ExamUserRank] [tinyint] NULL,
 CONSTRAINT [PK_Exams] PRIMARY KEY CLUSTERED 
(
    [ExamID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [VELES]
GO
ALTER TABLE [dbo].[Exams]  WITH CHECK ADD  CONSTRAINT [FK_Exams_Bases] FOREIGN KEY([ExamBase])
REFERENCES [dbo].[Bases] ([BaseID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Exams]  WITH NOCHECK ADD  CONSTRAINT [FK_Exams_Periods] FOREIGN KEY([ExamPeriod])
REFERENCES [dbo].[Periods] ([PeriodID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Exams]  WITH NOCHECK ADD  CONSTRAINT [FK_Exams_Users] FOREIGN KEY([ExamUserID])
REFERENCES [dbo].[Users] ([UserID])
ON UPDATE CASCADE
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Exams] CHECK CONSTRAINT [FK_Exams_Users]
GO
ALTER TABLE [dbo].[Exams]  WITH CHECK ADD  CONSTRAINT [UserRanks_Exams_FK1] FOREIGN KEY([ExamUserRank])
REFERENCES [dbo].[UserRanks] ([RankID])
ON UPDATE CASCADE
ON DELETE CASCADE

Answer 1

I've solved the problem.

In the relationship window, there was an option called Enforce Foreign Key Constraint, which was set to "No". I set it to "Yes" and now row deletion works well.

Answer 2

Can you show your table structure more concretely? It sound like you have the PK/FK the wrong way around.

Deleting the FK part (child) does not do anything to the PK record (parent). Only when you delete the PK records does it cascade to the child records that link to it.