I have identified 3 situations.
- A student with no enrollments.
- A student with enrollments but no grades.
- A student with enrollments and grades.
There is a trigger on the enrollments table to calculate GPA. If a student has grades it will update or insert an entry into the GPA table; no grades, no GPA table entry.
I can delete a student with no enrollments (#1). I can delete a student with enrollments and grades (#3 above). But I can not delete a student with enrollments but no grades (#2). I get a reference constraint violation.
The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.GPA_dbo.Student_StudentID". The conflict occurred in database "", table "dbo.GPA", column 'StudentID'.
If I could not delete a new student with no enrollments (and no GPA entry) then I would understand the constraint violation, but I can delete that student. It's a student with enrollments and no grades (and still no GPA entry) that I can not delete.
I have patched my trigger so I can go forward. Now, if you have enrollments the trigger inserts you into the GPA table no matter what. But I don't understand the underlying problem. Any explanation would be most appreciated.
For what it's worth:
- Visual Studio 2013 Professional.
- IIS express (internal to VS2013).
- ASP.NET Web App using EntityFramework 6.1.1.
- MS SQL Server 2014 Enterprise.
- GPA.Value is nullable.
- Enrollment.GradeID is nullable.
Here is a snippet of the database:
—EDIT—
The tables are all created by the EntityFramework, I used SQL Server Management Studio to produce these.
Here are the create table statements with constraints.:
GPA
table:
CREATE TABLE [dbo].[GPA](
[StudentID] [int] NOT NULL,
[Value] [float] NULL,
CONSTRAINT [PK_dbo.GPA] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[GPA] WITH CHECK
ADD CONSTRAINT [FK_dbo.GPA_dbo.Student_StudentID]
FOREIGN KEY([StudentID])
REFERENCES [dbo].[Student] ([ID])
ALTER TABLE [dbo].[GPA]
CHECK CONSTRAINT [FK_dbo.GPA_dbo.Student_StudentID]
Enrollment
table:
CREATE TABLE [dbo].[Enrollment](
[EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[GradeID] [int] NULL,
CONSTRAINT [PK_dbo.Enrollment] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Enrollment] WITH CHECK
ADD CONSTRAINT [FK_dbo.Enrollment_dbo.Course_CourseID]
FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
ON DELETE CASCADE
ALTER TABLE [dbo].[Enrollment]
CHECK CONSTRAINT [FK_dbo.Enrollment_dbo.Course_CourseID]
ALTER TABLE [dbo].[Enrollment] WITH CHECK
ADD CONSTRAINT [FK_dbo.Enrollment_dbo.Grade_GradeID]
FOREIGN KEY([GradeID])
REFERENCES [dbo].[Grade] ([GradeID])
ALTER TABLE [dbo].[Enrollment]
CHECK CONSTRAINT [FK_dbo.Enrollment_dbo.Grade_GradeID]
ALTER TABLE [dbo].[Enrollment] WITH CHECK
ADD CONSTRAINT [FK_dbo.Enrollment_dbo.Student_StudentID]
FOREIGN KEY([StudentID])
REFERENCES [dbo].[Student] ([ID])
ON DELETE CASCADE
ALTER TABLE [dbo].[Enrollment]
CHECK CONSTRAINT [FK_dbo.Enrollment_dbo.Student_StudentID]
Student
table:
CREATE TABLE [dbo].[Student](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EnrollmentDate] [datetime] NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_dbo.Student] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here are the triggers:
CREATE TRIGGER UpdateGPAFromUpdateDelete
ON Enrollment
AFTER UPDATE, DELETE AS
BEGIN
DECLARE @UpdatedStudentID AS int
SELECT @UpdatedStudentID = StudentID FROM DELETED
EXEC MergeGPA @UpdatedStudentID
END
CREATE TRIGGER UpdateGPAFromInsert
ON Enrollment
AFTER INSERT AS
--DECLARE @InsertedGradeID AS int
--SELECT @InsertedGradeID = GradeID FROM INSERTED
--IF @InsertedGradeID IS NOT NULL
BEGIN
DECLARE @InsertedStudentID AS int
SELECT @InsertedStudentID = StudentID FROM INSERTED
EXEC MergeGPA @InsertedStudentID
END
The patch to move forward was to comment out those lines in the AFTER INSERT
trigger.
Here is the stored procedure:
CREATE PROCEDURE MergeGPA @StudentID int AS
MERGE GPA AS TARGET
USING (SELECT @StudentID) as SOURCE (StudentID)
ON (TARGET.StudentID = SOURCE.StudentID)
WHEN MATCHED THEN
UPDATE
SET Value = (SELECT Value FROM GetGPA(@StudentID))
WHEN NOT MATCHED THEN
INSERT (StudentID, Value)
VALUES(SOURCE.StudentID, (SELECT Value FROM GetGPA(@StudentID)));
Here is the database function:
CREATE FUNCTION GetGPA (@StudentID int)
RETURNS TABLE
AS RETURN
SELECT ROUND(SUM (StudentTotal.TotalCredits) / SUM (StudentTotal.Credits), 2) Value
FROM (
SELECT
CAST(Credits as float) Credits
, CAST(SUM(Value * Credits) as float) TotalCredits
FROM
Enrollment e
JOIN Course c ON c.CourseID = e.CourseID
JOIN Grade g ON e.GradeID = g.GradeID
WHERE
e.StudentID = @StudentID AND
e.GradeID IS NOT NULL
GROUP BY
StudentID
, Value
, e.courseID
, Credits
) StudentTotal
Here is the debug output from the controller's delete method, the select statement is the method querying what to delete. This student has 3 enrollments, the REFERENCE
constraint issue happens when the 3rd enrollment deletes. I presume EF is using a transaction because the enrollments are not deleted.
iisexpress.exe Information: 0 : Component:SQL Database;Method:SchoolInterceptor.ReaderExecuted;Timespan:00:00:00.0004945;Properties:
Command: SELECT
[Project2].[StudentID] AS [StudentID],
[Project2].[ID] AS [ID],
[Project2].[EnrollmentDate] AS [EnrollmentDate],
[Project2].[LastName] AS [LastName],
[Project2].[FirstName] AS [FirstName],
[Project2].[Value] AS [Value],
[Project2].[C1] AS [C1],
[Project2].[EnrollmentID] AS [EnrollmentID],
[Project2].[CourseID] AS [CourseID],
[Project2].[StudentID1] AS [StudentID1],
[Project2].[GradeID] AS [GradeID]
FROM ( SELECT
[Limit1].[ID] AS [ID],
[Limit1].[EnrollmentDate] AS [EnrollmentDate],
[Limit1].[LastName] AS [LastName],
[Limit1].[FirstName] AS [FirstName],
[Limit1].[StudentID] AS [StudentID],
[Limit1].[Value] AS [Value],
[Extent3].[EnrollmentID] AS [EnrollmentID],
[Extent3].[CourseID] AS [CourseID],
[Extent3].[StudentID] AS [StudentID1],
[Extent3].[GradeID] AS [GradeID],
CASE WHEN ([Extent3].[EnrollmentID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (2)
[Extent1].[ID] AS [ID],
[Extent1].[EnrollmentDate] AS [EnrollmentDate],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName],
[Extent2].[StudentID] AS [StudentID],
[Extent2].[Value] AS [Value]
FROM [dbo].[Student] AS [Extent1]
LEFT OUTER JOIN [dbo].[GPA] AS [Extent2] ON [Extent1].[ID] = [Extent2].[StudentID]
WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Enrollment] AS [Extent3] ON [Limit1].[ID] = [Extent3].[StudentID]
) AS [Project2]
ORDER BY [Project2].[StudentID] ASC, [Project2].[ID] ASC, [Project2].[C1] ASC:
iisexpress.exe Information: 0 : Component:SQL Database;Method:SchoolInterceptor.NonQueryExecuted;Timespan:00:00:00.0012696;Properties:
Command: DELETE [dbo].[Enrollment]
WHERE ([EnrollmentID] = @0):
iisexpress.exe Information: 0 : Component:SQL Database;Method:SchoolInterceptor.NonQueryExecuted;Timespan:00:00:00.0002634;Properties:
Command: DELETE [dbo].[Enrollment]
WHERE ([EnrollmentID] = @0):
iisexpress.exe Information: 0 : Component:SQL Database;Method:SchoolInterceptor.NonQueryExecuted;Timespan:00:00:00.0002512;Properties:
Command: DELETE [dbo].[Enrollment]
WHERE ([EnrollmentID] = @0):
iisexpress.exe Error: 0 : Error executing command: DELETE [dbo].[Student]
WHERE ([ID] = @0) Exception: System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.GPA_dbo.Student_StudentID". The conflict occurred in database "<databasename>", table "dbo.GPA", column 'StudentID'.
The statement has been terminated.
Best Answer
It's a question of timing. Consider deleting StudentID #1:
Student
tableEnrollment
GPA
->Student
is checkedMergeGPA
At this point,
MergeGPA
checks to see if there is an entry for Student #1 in theGPA
table. There is not (otherwise the FK check in step 3 would have raised an error).So, the
WHEN NOT MATCHED
clause inMergeGPA
attempts toINSERT
a row inGPA
for StudentID #1. This attempt fails (with the FK error) because StudentID #1 has already been deleted from theStudent
table (at step 1).