April 17, 2012 at 10:42 am
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
April 17, 2012 at 10:45 am
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.
April 17, 2012 at 10:46 am
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...
April 17, 2012 at 11:14 am
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
April 17, 2012 at 11:25 am
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.
April 17, 2012 at 12:05 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 1:03 pm
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.
Jared
CE - Microsoft
April 17, 2012 at 1:08 pm
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.
Jared
CE - Microsoft
April 17, 2012 at 1:08 pm
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
April 17, 2012 at 1:14 pm
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.
Jared
CE - Microsoft
April 17, 2012 at 1:16 pm
SQLKnowItAll (4/17/2012)
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.
What, and not have the fun of watching us spin our wheels? :w00t:
April 17, 2012 at 1:18 pm
Lynn Pettis (4/17/2012)
SQLKnowItAll (4/17/2012)
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.
What, and not have the fun of watching us spin our wheels? :w00t:
I get enough of that at work :hehe: SSC is my vacation spot.
Jared
CE - Microsoft
April 17, 2012 at 1:25 pm
This is hard for me too everytime to post on T-SQL forum, try to make it simple to get some hint, and not post all complicated table structures and sprocs, but it ends up I have to post all those tables and sprocs that I concern people won't look at them after seeing so much posted text.
Don't have problem in DBA questions in DBA forum.
April 17, 2012 at 1:30 pm
sqlfriends (4/17/2012)
This is hard for me too everytime to post on T-SQL forum, try to make it simple to get some hint, and not post all complicated table structures and sprocs, but it ends up I have to post all those tables and sprocs that I concern people won't look at them after seeing so much posted text.Don't have problem in DBA questions in DBA forum.
One of the best things about compiling all the information to provide details for a question is you will be surprised how often that process will lead to the answer for your own question.
ddl, sample data and desired output should ALWAYS be provided. Those three things are the basis of the language. Without them there is little anyone can do. It also highly important to provide as much details as possible about the process.
I can remember a thread once where we spent dozens of posts trying to figure out why something didn't work. It turned out the OP was trying to create a proc to be called from a udf. We had to back up and take a different approach but it would have saved tons of time just by the OP providing the details up front.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 1:31 pm
sqlfriends (4/17/2012)
This is hard for me too everytime to post on T-SQL forum, try to make it simple to get some hint, and not post all complicated table structures and sprocs, but it ends up I have to post all those tables and sprocs that I concern people won't look at them after seeing so much posted text.Don't have problem in DBA questions in DBA forum.
The hint is exactly what the error is telling you. you are trying to delete a row that cannot be deleted because of a reference from another table. What table references the row you are trying to delete? Tat's all you have to figure out. If you want us to tell you that, we need exact errors and exact SQL.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply