Home Forums SQL Server 2008 T-SQL (SS2K8) The DELETE statement conflicted with the REFERENCE constraint RE: The DELETE statement conflicted with the REFERENCE constraint

  • 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