Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The DELETE statement conflicted with the REFERENCE constraint Expand / Collapse
Author
Message
Posted Tuesday, April 17, 2012 10:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 1,773, Visits: 3,206
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
Post #1285111
Posted Tuesday, April 17, 2012 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 23,290, Visits: 32,017
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.



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)
Post #1285117
Posted Tuesday, April 17, 2012 10:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1285119
Posted Tuesday, April 17, 2012 11:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 1,773, Visits: 3,206
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


  Post Attachments 
tablescript.txt (13 views, 3.72 KB)
Post #1285137
Posted Tuesday, April 17, 2012 11:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 23,290, Visits: 32,017
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.



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)
Post #1285146
Posted Tuesday, April 17, 2012 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 13,302, Visits: 12,166
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 Moden's 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)
Post #1285160
Posted Tuesday, April 17, 2012 1:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:40 AM
Points: 2,692, Visits: 3,383
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1285209
Posted Tuesday, April 17, 2012 1:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:40 AM
Points: 2,692, Visits: 3,383
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1285213
Posted Tuesday, April 17, 2012 1:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 1,773, Visits: 3,206
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
Post #1285214
Posted Tuesday, April 17, 2012 1:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:40 AM
Points: 2,692, Visits: 3,383
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1285220
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse