How to set the constraints, add "on delete casecade"?...

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hey everyone,

I am using a delete command on a grid,the primary key of this table in the foreign key in an another table.So,i'm getting this exception while performing delete operation--The DELETE statement conflicted with the REFERENCE constrain.How to resolve this.The table from which i'm trying to delete is tblProducts with primary key Pid.And Pid is foreign key to table tblOrderItems.

I saw a solution but can't understand how to use it.It was--

From management studio, right click --> edit, and toward the bottom where it sets the constraints, add "on delete casecade" and execute just the alter.

Help me resolve this.

Thanks
Amit

Posted 27-Oct-10 1:12am

AmitChoudhary10

Add a Solution

Comments

Rod Kemp 27-Oct-10 7:33am

This is just a bad idea, you would be deleting order items also are the primary keys to the order items tables used anywhere and so on.
Normal solution for this sort of thing is to set a "deleted" flag.

AmitChoudhary10 27-Oct-10 7:36am

how can i do this?..i am getting exception while deleting item.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RECSEN · Accepted Answer · 2010-10-28T01:37:00

Solution 1

First you should delete reference key table record and then delete primary key record Like

delete tblOrderItems where Pid = 'p100';
delete tblProducts where pid = 'p100';

Posted 28-Oct-10 1:37am

RECSEN

Comments

AmitChoudhary10 28-Oct-10 8:04am

that means for deleting row from this table,i'll have to delete all the rows from different tables where its primary key is referenced?...

RECSEN · Accepted Answer · 2010-10-28T02:07:00

Solution 2

Yes you must delete reference table row first then only you can able to delete primary key table record ....

Posted 28-Oct-10 2:07am

RECSEN

Comments

AmitChoudhary10 28-Oct-10 8:27am

i am using this query,its not working,i want to delete from tblProducts,could you help a bit--

"DELETE FROM tblProducts AS P INNER JOIN tblOrderItems AS OI ON OI.Pid = P.Pid where Pid=@Pid"

AmitChoudhary10 28-Oct-10 8:29am

its giving exception "INCORRECT syntax near AS"

RECSEN · Accepted Answer · 2010-10-28T02:35:00

Solution 3

Create a Procedure or run the below procedure on your machine it will work
No need inner join for deleting reference key u can just delete as it is

SQL

Create Procedure DeleteProduct
@PID varchar(100)
AS
begin transaction
DELETE tblOrderItems where Pid=@PID
DELETE tblProducts where Pid=@PID
IF @@error <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END

Posted 28-Oct-10 2:35am

RECSEN

Updated 28-Oct-10 2:36am

v2

Comments

AmitChoudhary10 28-Oct-10 8:43am

still not working.Stored procedure is giving errors

USE [ChalkHill]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spDeleteItem]
@Pid = varchar(100)

As
begin transaction
SET NOCOUNT ON;
DELETE tblOrderItems where Pid=@Pid
DELETE tblProducts where Pid=@Pid
IF @@error <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END

AmitChoudhary10 28-Oct-10 8:51am

my bad,thats done,thanks man :-)