Primary Key Foreign Key Constrain not working [Resolved]

Posted by Raj.Trivedi under Sql Server on 8/8/2013 | Points: 10 | Views : 1128 | Status : [Member] [MVP] | Replies : 3
Hello Team,

I am having 2 table

Table 1 Category

Table 2 Sub Category

Here is the script for table 1

CREATE TABLE [dbo].[tbl_ProductCategory](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](100) NOT NULL,
[CategoryDescription] [nvarchar](max) NULL,
[IsActive] [bit] NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedBy] [int] NULL,
[UpdatedDate] [datetime] NULL,
CONSTRAINT [PK_Category_CategoryId] PRIMARY KEY CLUSTERED
(
[CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_ProductCategory] ADD DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[tbl_ProductCategory] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

Script for Table 2

USE [POPL]
GO

/****** Object: Table [dbo].[tbl_ProductSubCategory] Script Date: 08/08/2013 21:34:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_ProductSubCategory](
[SubCategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryId] [int] NOT NULL,
[SubCategoryName] [nvarchar](100) NOT NULL,
[SubCategoryDescription] [nvarchar](max) NULL,
[IsActive] [bit] NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedBy] [int] NULL,
[UpdatedDate] [datetime] NULL,
CONSTRAINT [PK_SubCategory_SubCategoryId] PRIMARY KEY CLUSTERED
(
[SubCategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_ProductSubCategory] WITH CHECK ADD CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[tbl_ProductCategory] ([CategoryId])
ON DELETE CASCADE

GO

ALTER TABLE [dbo].[tbl_ProductSubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO

ALTER TABLE [dbo].[tbl_ProductSubCategory] ADD DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[tbl_ProductSubCategory] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

Now if we check i have added the foreign key Constraint so that is i delete a category which is having data related to Subcategory then it should thrown an error but unfortunately it is not.
When i tried deleting it from my Asp.Net app it deleted successfully that is wrong.

I need to know where i am making a glitch

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
that is happend because of ON DELETE CASCADE clause. just delete that clause from the script. then you will get error in the case of parent table values. you can achieve default behaviour delete operation by using ON DELETE NO ACTION clause.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
on delete cascade means deletes all related child(sub) tables records if you delete parent(super) table record...
i
If you would like to get error in the case of deleting parent record without deleting related child record, you should use ON DELETE NO ACTION in the alter table statement.
If it helps you or directs towards the solution, MARK IT AS ANSWER



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 8/9/2013 [Member] Starter | Points: 25

Login to post response