DELETE statement conflicted with COLUMN 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 2000 Forums

Transact-SQL (2000)

DELETE statement conflicted with COLUMN REFERENCE

Author

Topic

brettjason@cox.net
Starting Member

5 Posts

Posted - 2008-11-10 : 02:21:53

Any Idea why the Stored Procedure below would cause this error? Thanks in advance for any insight. Brett

Error:
"DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_workout_program_set_workout_program_exercise'. The conflict occurred in database 'TPI_DATA', table 'workout_program_set', column 'prog_ex_id'.
The statement has been terminated.
Command being executed:spDeleteWorkoutSessionForEval"

SP:

/* Removes a Workout Session and all of its child table records by inferring its host Workout
from the Workout's Eval ID */

CREATE Procedure spDeleteWorkoutSessionForEval
@eval_id Int

As
SET NOCOUNT ON

BEGIN TRANSACTION

Delete workout_program_set From workout_program_set a inner join workout_program_exercise b on a.prog_ex_id = b.id inner join workout_program_session c on b.session_id = c.id inner join workout_program d on c.program_id = d.id
Where d.eval_id = @eval_id

IF (@@ERROR <> 0) BEGIN
ROLLBACK TRANSACTION
RETURN
END

Delete workout_program_exercise From workout_program_exercise b inner join workout_program_session c on b.session_id = c.id inner join workout_program d on c.program_id = d.id
Where d.eval_id = @eval_id

IF (@@ERROR <> 0) BEGIN
ROLLBACK TRANSACTION
RETURN
END

Delete workout_program_session From workout_program_session c inner join workout_program d on c.program_id = d.id
Where d.eval_id = @eval_id

IF (@@ERROR <> 0) BEGIN
ROLLBACK TRANSACTION
RETURN
END

COMMIT TRANSACTION

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 02:28:14

Yup. that means you've some records existing in workout_program_exercise table that have value of 'prog_ex_id' column same as the ones which you're trying to delete from workout_program_set table. so if you want above deletion to happen make sure you delete the dependent records from workout_program_exercise before deleting from workout_program_set table.

brettjason@cox.net
Starting Member

5 Posts

Posted - 2008-11-10 : 02:34:36

But prog_ex_id is in workout_program_set and it's a foreign key reference to the primary key (id) in workout_program_exercise. I assumed I'm deleting records in the correct order. Each workout_program_exercise record will have multiple related records in workout_program_set so delete the workout_program_set records first, correct? Or am I missing something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 02:43:56

Ok.. that sounds fine

b/w how does workout_program_session & workout_program relate to these tables? do they also have a foreign key reerences?

brettjason@cox.net
Starting Member

5 Posts

Posted - 2008-11-10 : 02:49:29

workout_program.id (pk) = workout_program_session.program_id (fk)
workout_program_session.id (pk) = workout_program_exercise.session_id (fk)
workout_program_exercise.id (pk) = workout_program_set.prog_ex_id

so I deleted the records in reverse order to remove all related foreign keys first. All relationships are 1 to many.

Brett

brettjason@cox.net
Starting Member

5 Posts

Posted - 2008-11-10 : 02:55:54

Ok, here's a question, is it at all possible for the second delete to start while the first delete is still working? There are a lot of records in these tables. I assumed that in stored procedures each step goes in order and waits for the previous step to finish before starting. Is this a safe assumption?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 03:15:28

it is a safe assumption i think. can i assume by the time you fire the baove delete you've removed all dependent records from other three tables?

brettjason@cox.net
Starting Member

5 Posts

Posted - 2008-11-10 : 03:18:04

The above delete removes all dependent records in order. 1. Set 2. Exercise 3. Session. I ran sp_help on all 4 tables to make sure there are no other foreign keys that I am missing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources