October 18, 2008 at 2:58 am
Hi,
I have implemented a referential integrity in my tables.
and I have ASP application software.But when I delete the record
from application then records also delete from database and application.
So please tell me what should I do to prevent my record?
Thanks
Sunil.
October 18, 2008 at 3:14 am
Um, can you maybe give a bit more info? What do the tables look like? What are the foreign key definitions? What are you deleting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2008 at 3:30 am
My Table Structure look like as below....
CREATE TABLE [dbo].[CMFP](
[APPROVE] [bit] NOT NULL CONSTRAINT [DF__CMFP__APPROV__37B48FBB] DEFAULT ((0)),
[ONHOLD] [bit] NOT NULL CONSTRAINT [DF__CMFP__ONHOLD__38A8B3F4] DEFAULT ((0)),
[REJECT] [bit] NOT NULL CONSTRAINT [DF__CMFP__REJECT__399CD82D] DEFAULT ((0)),
[CMFPID] [int] IDENTITY(1,1) NOT NULL,
[CMFP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORTTO] [int] NULL CONSTRAINT [DF_CMFP_REGION] DEFAULT ((0)),
[ACTIVE] [bit] NOT NULL CONSTRAINT [DF_CMFP_ACTIVEFLAG] DEFAULT ((0)),
CONSTRAINT [PK__CMFP__36C06B82] PRIMARY KEY CLUSTERED
(
[CMFPID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[CMFPDET](
[CMFPID] [int] NULL,
[CMFPDETID] [int] IDENTITY(1,1) NOT NULL,
[YEAR] [int] NULL CONSTRAINT [DF_CMFPDET_YEAR] DEFAULT ((0)),
[QUARTER] [int] NULL CONSTRAINT [DF_CMFPDET_QUARTER] DEFAULT ((0)),
[QUARTERTARGET] [money] NULL CONSTRAINT [DF_CMFPDET_QUARTERTARGET] DEFAULT ((0)),
[ACHEIEVEMENT] [money] NULL CONSTRAINT [DF_CMFPDET_ACHEIEVEMENT] DEFAULT ((0)),
[INDICATOR] [int] NOT NULL CONSTRAINT [DF_CMFPDET_INDICATOR] DEFAULT ((0)),
[INCENTIVE] [money] NULL CONSTRAINT [DF_CMFPDET_INCENTIVE] DEFAULT ((0)),
CONSTRAINT [PK__CMFPDET__3F55B183] PRIMARY KEY CLUSTERED
(
[CMFPDETID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [InfolineCRM]
GO
ALTER TABLE [dbo].[CMFPDET] WITH CHECK ADD CONSTRAINT [fk_CMFPDET_CMFPID] FOREIGN KEY([CMFPID])
REFERENCES [dbo].[CMFP] ([CMFPID])
My problem is that when I delete the records from master table then
records also delete from Foreign key table.So What should do for this?
thanks
October 18, 2008 at 3:59 am
With the table defs and foreign key that you've posted, that's not possible, unless there's an explicit delete been done from both tables.
-- using the definition posted above
INSERT INTO CMFP (CMFP)
values ('A')
INSERT INTO CMFP (CMFP)
values ('B')
INSERT INTO CMFP (CMFP)
values ('C')
INSERT INTO CMFP (CMFP)
values ('D')
INSERT INTO CMFP (CMFP)
values ('E')
Insert into CMFPDET (CMFPID)
values (1)
Insert into CMFPDET (CMFPID)
values (2)
Insert into CMFPDET (CMFPID)
values (2)
Insert into CMFPDET (CMFPID)
values (3)
Insert into CMFPDET (CMFPID)
values (4)
Insert into CMFPDET (CMFPID)
values (5)
Insert into CMFPDET (CMFPID)
values (5)
Insert into CMFPDET (CMFPID)
values (5)
SELECT * from CMFPDET -- 8 rows
SELECT * FROM CMFP -- 5 rows
DELETE FROM CMFPDET WHERE CMFPDETID = 6 -- 1 row affected
SELECT * from CMFPDET -- 7 rows
SELECT * FROM CMFP -- 5 rows -- delete done from master, foreign key table not affected
DELETE FROM CMFP WHERE CMFPID = 4
/*Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "fk_CMFPDET_CMFPID".
The conflict occurred in database "tempdb", table "dbo.CMFPDET", column 'CMFPID' */
Can you have a look at the code that the app is using to do the delete?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2008 at 4:06 am
Yes I got this message what you have shown me but when I delete records from ASP application software my referential intigrity does not works. Records delete from application and databse also.
So what is the problem? Why records delete from databse and application both.
Please give me a solution.
Thanks.
October 18, 2008 at 4:22 am
The only way that could be happening is if the ASP app is deleting from both tables. Can you find the delete statements that it's running? Use profiler if you don't have access to the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply