Command Execution Exception: The INSERT statement conflicted with the FOREIGN KEY constraint

Question

I have a table named student I want to assign a fee or update (if present), I am looping over them which worked fine until I deleted a student. So now whenever I run the stored procedure it shows error.

Here is my code.

ALTER PROC [dbo].[sp_AutoAssignFeeUpdate]
(
    @FeeID int,
    @FeeAmount int,
    @Fine int,
    @DueDate date,
    @AppliedON date,
    @FeeMonth varchar(30)
)
AS 

--- Variables Using in Loops
DECLARE @LoopCounter INT , @MaxStudentID INT, @StdID INT, @FID INT

-- Setting Counter From the count of students in student table if they are 'Active'

SELECT @LoopCounter = min(AdmissionNumber) , @MaxStudentID = max(AdmissionNumber) 
FROM StudentTable

-- WHILE Loop Condition 
WHILE(@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxStudentID )
BEGIN
    --- SELECT IDs all Active students and matching with counter
   SELECT @StdID = AdmissionNumber 
   FROM StudentTable WHERE AdmissionNumber = @LoopCounter AND Active = 'True'

      --- CHECK IF ROW EXITS
   SELECT @StdID = AdmissionNumber
   FROM FeeAssociationTable
   IF EXISTS ( SELECT FeeMonth FROM FeeAssociationTable 
                    WHERE @LoopCounter = AdmissionNumber AND FeeID = @FeeID AND FeeMonth = @FeeMonth)
        BEGIN
            UPDATE FeeAssociationTable
            SET FeeAmount = @FeeAmount, Fine = @Fine , DueDate = @DueDate
            WHERE @LoopCounter = AdmissionNumber AND FeeID = @FeeID
            AND  FeeMonth = @FeeMonth
        END

    ELSEBEGIN
        INSERT FeeAssociationTable 
        (FeeID, AdmissionNumber, FeeAmount, FeeMonth, DueDate, Fine, AppliedOn, [Status])

        VALUES 
        (@FeeID, @LoopCounter, @FeeAmount, @FeeMonth, @DueDate, @Fine, @AppliedON, 'Pending')
    END
   SET @LoopCounter  = @LoopCounter  + 1        
END

This is working if the Ids are continuous. What should I do if there is an Id missing or how to skip that specific number which is not present in the studentTable.

Explanation:

The loop take the initial value of min(id) from studentTable set as counter, and final value of max(id).

The loop compares both values id in studentTable and counter of the loop.

Then for each counter student in the table the the fee is assigned.

INSERT FeeAssociationTable 
        (FeeID, AdmissionNumber, FeeAmount, FeeMonth, DueDate, Fine, AppliedOn, [Status])

        VALUES 
        (@FeeID, @LoopCounter, @FeeAmount, @FeeMonth, @DueDate, @Fine, 

The problem is here, while inserting I am using @LoopCounter. Lets say @LoopCounter = 100 but StudentTable is skipping 100 and there is 101 there. The conflict rises. Because the SQL can't find the **100** id in the studentTable.

Thanks in Advance.

Answer 1

As I said in a comment, this whole thing looks like it can be replaced by a MERGE. Don't do things one step at a time when you can tell the server what to do with the entire set of rows.

Something like:

MERGE INTO FeeAssociationTable t
USING (SELECT AdmissionNumber, @FeeID as FeeID, @FeeMonth as FeeMonth FROM StudentTable
       WHERE Active = 'True') s
ON t.AdmissionNumber = s.AdmissionNumber AND
t.FeeID = s.FeeID AND
t.FeeMonth = s.FeeMonth
WHEN MATCHED THEN UPDATE SET FeeAmount = @FeeAmount, Fine = @Fine , DueDate = @DueDate
WHEN NOT MATCHED THEN INSERT
(FeeID, AdmissionNumber, FeeAmount, FeeMonth, DueDate, Fine, AppliedOn, [Status])
VALUES
(@FeeID, s.AdmissionNumber, @FeeAmount, @FeeMonth, @DueDate, @Fine, @AppliedON, 'Pending');

Not sure I've got all of the conditions quite right, but you should be able to see what I'm driving at, I hope.

---

Your actual issue could have been "solved" by replacing:

SET @LoopCounter  = @LoopCounter  + 1

with:

SELECT @LoopCounter = MIN(AdmissionNumber) FROM StudentTable
   WHERE Active = 'True' and AdmissionNumber > @LoopCounter

but don't do that, please.

Answer 2

Man , you should use a For each . For example:

DECLARE yourCursor CURSOR LOCAL STATIC
FOR SELECT AdmissionNumber
FROM StudentTable

OPEN yourCursor
FETCH NEXT FROM yourCursor INTO @StdID
WHILE @@FETCH_STATUS = 0
BEGIN

/*
 CHECK IF EXIST FOR UPDATE OR INSERT
*/

FETCH NEXT FROM yourCursor INTO @StdID
END
CLOSE yourCursor
DEALLOCATE yourCursor
GO

This replace your while.