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