The DELETE statement conflicted with the REFERENCE

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

SQL Server 2005 Forums

Transact-SQL (2005)

The DELETE statement conflicted with the REFERENCE

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2008-08-05 : 15:06:11

I'm getting the following error when trying to run a delete statement:

The DELETE statement conflicted with the REFERENCE constraint "FK_information_center_file". The conflict occurred in database "Public_Data", table "dbo.information_center", column 'file_id'.
The statement has been terminated.

Here is the stored proceudre that I'm calling to run the delete statement:

CREATE PROCEDURE [dbo].[spDeleteInformationCenterFile]
@file_id INT,
@result int output
AS

set @result=0

BEGIN
DELETE FROM dbo.[information_center]
WHERE [file_id] = @file_id

IF @@rowcount>0
SET @result=1

END

Here are the tables:

CREATE TABLE [dbo].[information_center](
[information_center_id] [int] IDENTITY(1,1) NOT NULL,
[category_id] [int] NOT NULL,
[file_id] [int] NOT NULL,
[description] [varchar](55) NOT NULL,
CONSTRAINT [PK_information_center] PRIMARY KEY CLUSTERED
(
[information_center_id] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[information_center] WITH NOCHECK ADD CONSTRAINT [FK_information_center_file] FOREIGN KEY([file_id])
REFERENCES [dbo].[file] ([file_id])
GO
ALTER TABLE [dbo].[information_center] CHECK CONSTRAINT [FK_information_center_file]
GO
ALTER TABLE [dbo].[information_center] WITH NOCHECK ADD CONSTRAINT [FK_information_center_intormation_center_category] FOREIGN KEY([category_id])
REFERENCES [dbo].[information_center_category] ([category_id])
GO
ALTER TABLE [dbo].[information_center] CHECK CONSTRAINT [FK_information_center_intormation_center_category]

CREATE TABLE [dbo].[information_center_category](
[category_id] [int] IDENTITY(1,1) NOT NULL,
[category_name] [varchar](35) NOT NULL,
CONSTRAINT [PK_intormation_center_category] PRIMARY KEY CLUSTERED
(
[category_id] 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
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[file](
[file_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](75) NULL,
[path] [varchar](150) NULL,
[description] [varchar](250) NULL,
[username] [varchar](35) NULL,
[stamp] [datetime] NULL,
[system_file_name] [varchar](500) NULL,
CONSTRAINT [PK_file] PRIMARY KEY CLUSTERED
(
[file_id] 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
SET ANSI_PADDING OFF

In SQL Server 2005 under the table information_center node, there is columns, keys, constraints, triggers, indexes and statistics. Under the KEYS node, there are the following:
PK_information_center
FK_information_center_file
FK_information_center_information_center_category

I deleted this reference and then it worked. Why? What caused this? I don't know if deleting this key caused any harm, but the delete worked after deleting the key.

I restored the database back to its original state, so the key FK_information_center_file has been re-established.

What ill effects are caused by deleting this KEY? Is there a better way to handle the delete where I don't have to remove the key?

Any help is appreciated.
Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 15:10:28

You must delete all child data before deleting the parent. Do not remove the foreign key just to get around an error.

What tables are linked to dbo.information_center? Briefly looking at your code, perhaps just the file table? Did you delete all of the child data from file first?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 15:11:34

Your code should be like this:

DELETE f
FROM dbo.[file] f
INNER JOIN dbo.[information_center] ic
ON f.file_id = ic.file_id
WHERE ic.[file_id] = @file_id

DELETE
FROM dbo.[information_center]
WHERE [file_id] = @file_id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 15:15:18

Or setup a cascading delete so the records automatically get removed when the primary key is deleted.

-Dman100-
Posting Yak Master

210 Posts

Posted - 2008-08-05 : 15:33:08

I updated the procedure as follows:

ALTER PROCEDURE [dbo].[spDeleteInformationCenterFile]
@file_id INT,
@result int output
AS

set @result=0

BEGIN

DELETE f
FROM dbo.[file] f
INNER JOIN dbo.[information_center] ic
ON f.file_id = ic.file_id
WHERE ic.[file_id] = @file_id

DELETE FROM dbo.[information_center]
WHERE [file_id] = @file_id

IF @@rowcount>0
SET @result=1

END

When I tested the delete from my application, I got the same error:
The DELETE statement conflicted with the REFERENCE constraint "FK_information_center_file". The conflict occurred in database "Public_Data", table "dbo.information_center", column 'file_id'.
The statement has been terminated.

Thanks for your help. I appreciate it.
Regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 15:38:57

Sorry I missed a table. You'll also need to delete the child data from information_center_category.

DELETE icc
FROM dbo.[information_center_category] icc
INNER JOIN dbo.[information_center] ic
ON icc.category_id = ic.category_id
WHERE ic.[file_id] = @file_id

DELETE f
FROM dbo.[file] f
INNER JOIN dbo.[information_center] ic
ON f.file_id = ic.file_id
WHERE ic.[file_id] = @file_id

DELETE FROM dbo.[information_center]
WHERE [file_id] = @file_id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rpds
Starting Member

1 Post

Posted - 2010-08-19 : 14:34:54

Can you please give the soln for this error?
Also please confirm that in below script
DELETE icc ....etc
DELETE f ....etc

You mean DELETE icc.column_name or what?

DELETE icc
FROM dbo.[information_center_category] icc
INNER JOIN dbo.[information_center] ic
ON icc.category_id = ic.category_id
WHERE ic.[file_id] = @file_id

DELETE f
FROM dbo.[file] f
INNER JOIN dbo.[information_center] ic
ON f.file_id = ic.file_id
WHERE ic.[file_id] = @file_id

DELETE FROM dbo.[information_center]
WHERE [file_id] = @file_id

Regards,
RK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-19 : 14:36:58

RK,

DELETE means to delete an entire row, so you don't specify a column name. I provided the solution already two years ago, so I'm not sure what you are asking.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources