SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The DELETE statement conflicted with the REFERENCE constraint


The DELETE statement conflicted with the REFERENCE constraint

Author
Message
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 4008
I tried to delete records in some tables.

I got an error:
The DELETE statement conflicted with the REFERENCE constraint :
The DELETE statement conflicted with the REFERENCE constraint "FK_ProgramResult_ProgramSnapshot". The conflict occurred in database "MyDB", table "dbo.ProgramResult".

I know there is a foreign key existing, but I am deleting in the right order, I first delete the records in the ProgramResult table, then Delete from the ProgramSnapshot table.

So what could be wrong?

Please note I don't want to drop FK constraints then do the deleting for this case, because I have 20 tables in production to do the delete operation. I just want to do delete in the right order of tables.

Forignkey table first, then primary key tables.

Thanks
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39093 Visits: 38518
First thing to do is verify that you are deleting records from the correct tables in the correct order. Based on your error message it appears that the foreign key goes the other way.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4968 Visits: 5478
Ten Centuries rated!
Please provide DDL for both sides of REFERENCE constraint and SQL your are running.
I guess you just missing something in your order...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 4008
I specifically pay attention to the order. So I delete from the child first, then parent table.

But I still get the error, I also tried to reverse the order, I get the same message.

The table and forign key scripts are in the attachment.

The delete statement I run is like below:

PRINT 'DELETING FROM batSchoolRangeProgramResult'
DELETE a
FROM [Assignment_4122012].[dbo].[batSchoolRangeProgramResult] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean


PRINT 'DELETING FROM appSchoolRangeProgramSnapshot'
DELETE a
FROM [Assignment_4122012].[dbo].[appSchoolRangeProgramSnapshot] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean
Attachments
tablescript.txt (27 views, 3.00 KB)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39093 Visits: 38518
sqlfriends (4/17/2012)
I specifically pay attention to the order. So I delete from the child first, then parent table.

But I still get the error, I also tried to reverse the order, I get the same message.

The table and forign key scripts are in the attachment.

The delete statement I run is like below:

PRINT 'DELETING FROM batSchoolRangeProgramResult'
DELETE a
FROM [Assignment_4122012].[dbo].[batSchoolRangeProgramResult] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean


PRINT 'DELETING FROM appSchoolRangeProgramSnapshot'
DELETE a
FROM [Assignment_4122012].[dbo].[appSchoolRangeProgramSnapshot] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean


I'd like others opinions, but from what I read in the definitions, the correct order should be as follows.



PRINT 'DELETING FROM appSchoolRangeProgramSnapshot'
DELETE a
FROM [Assignment_4122012].[dbo].[appSchoolRangeProgramSnapshot] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean


PRINT 'DELETING FROM batSchoolRangeProgramResult'
DELETE a
FROM [Assignment_4122012].[dbo].[batSchoolRangeProgramResult] a
JOIN @ProcessIDToClean b ON a.ProcessID = b.ProcessID
WHERE createdby = @CreatedBy
AND schoolyear = @SchoolYearToClean






Are you getting the exact same error message when you run this in this order?

Please post the error message regardless.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25948 Visits: 17519
That's the way I read it too Lynn. Pretty clear that is how the assignment was made.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 3694
No, look:
ALTER TABLE [dbo].[batSchoolRangeProgramResult]  WITH CHECK ADD  CONSTRAINT [FK_batSchoolRangeProgramResult_appSchoolRangeProgramSnapshot] FOREIGN KEY([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])
REFERENCES [dbo].[appSchoolRangeProgramSnapshot] ([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])



So the constraint is on batSchoolRangeProgramResult and that table needs to be deleted from first. Makes me wonder if there are other FK constraints existing on that table.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 3694
OP, look at your first error:

"FK_ProgramResult_ProgramSnapshot". The conflict occurred in database "MyDB", table "dbo.ProgramResult".


EDIT: OP changed names in first post from the actual in the attached code.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 4008
SQLKnowItAll (4/17/2012)
No, look:
ALTER TABLE [dbo].[batSchoolRangeProgramResult]  WITH CHECK ADD  CONSTRAINT [FK_batSchoolRangeProgramResult_appSchoolRangeProgramSnapshot] FOREIGN KEY([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])
REFERENCES [dbo].[appSchoolRangeProgramSnapshot] ([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])



So the constraint is on batSchoolRangeProgramResult and that table needs to be deleted from first. Makes me wonder if there are other FK constraints existing on that table.


I agree what you said.I end up remove the where clause , because the processID should be enough to take care of the delete, I think the error is caused by the forignkey is not only processID, but with other columns like schoolyear, rangecd, ...

That may be diffrent in the two tables. So if I only use programID, it works fine.

maybe a little more information would help to understand, before the delete I use the following statement:

SELECT @SchoolYearToClean = 2011 ,
@CreatedBy = 'BatchAssignment' ,
@ProcessType = 'Simulation'


PRINT 'insert into temp table'
INSERT INTO @ProcessIDToClean
SELECT ProcessID
FROM [Assignment_4122012].[dbo].[appProcess]
WHERE processType = @ProcessType
AND processNm = 'BatchAssignment'
AND schoolyear = @SchoolYearToClean


So
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 3694
sqlfriends (4/17/2012)
SQLKnowItAll (4/17/2012)
No, look:
ALTER TABLE [dbo].[batSchoolRangeProgramResult]  WITH CHECK ADD  CONSTRAINT [FK_batSchoolRangeProgramResult_appSchoolRangeProgramSnapshot] FOREIGN KEY([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])
REFERENCES [dbo].[appSchoolRangeProgramSnapshot] ([ProcessID], [SchoolYear], [SchoolID], [RangeCd], [ProgramID])



So the constraint is on batSchoolRangeProgramResult and that table needs to be deleted from first. Makes me wonder if there are other FK constraints existing on that table.


I agree what you said.I end up remove the where clause , because the processID should be enough to take care of the delete, I think the error is caused by the forignkey is not only processID, but with other columns like schoolyear, rangecd, ...

That may be diffrent in the two tables. So if I only use programID, it works fine.

maybe a little more information would help to understand, before the delete I use the following statement:

SELECT @SchoolYearToClean = 2011 ,
@CreatedBy = 'BatchAssignment' ,
@ProcessType = 'Simulation'


PRINT 'insert into temp table'
INSERT INTO @ProcessIDToClean
SELECT ProcessID
FROM [Assignment_4122012].[dbo].[appProcess]
WHERE processType = @ProcessType
AND processNm = 'BatchAssignment'
AND schoolyear = @SchoolYearToClean


So


If you are going to keep giving us new information, we can't help you. This is frustrating. Give us all of the information at once please.This is a stored procedure? Give the entire stored procedure then.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search