代码之家 › 专栏 › 技术社区 › Farhan

命令执行异常:INSERT语句与外键约束冲突

stored-procedures sql-server sql

Farhan · 技术社区 · 6 年前

我有一个名为student的表我想分配一个费用或更新(如果存在的话),我正在循环它们,直到我删除了一个学生。所以现在每当我运行存储过程时,它就会显示错误。

这是我的密码。

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

如果id是连续的,这就可以工作。如果缺少Id,或者如何跳过studentTable中不存在的特定数字,我应该怎么做。

循环的初始值为 min(id) 从 studentTable 设置为计数器,且最终值为 max(id)

循环比较两个值 id 在里面循环计数器。

然后,为表中的每个柜台学生分配费用。

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

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

问题就在这里,插入时我正在使用@LoopCounter。比如说 @LoopCounter = 100 但是 StudentTable 正在跳过那里有101个。冲突升级。因为SQL找不到 **100** studentTable中的id。

提前谢谢。

2 回复 | 直到 6 年前

Damien_The_Unbeliever 6 年前

正如我在评论中所说的,整个事情看起来可以用 MERGE . 不要做事 一步一步 全套的 一排排的。

类似于:

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');

我不确定我的所有条件是否都很好,但我希望你能看到我在开什么车。

SET @LoopCounter  = @LoopCounter  + 1

使用:

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

但请不要那样做。

Ignacio Alberto Loyte 6 年前

伙计,你应该每个都用a。例如:

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