foreign key constraint violation issue

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].GPAWITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER T

It's a question of timing. Consider deleting StudentID #1:

• The row is deleted from the Student table

• The cascade delete removes corresponding rows from Enrollment

• The foreign key relationship GPA -> Student is checked

• The trigger fires, calling MergeGPA

At this point, MergeGPA checks to see if there is an entry for Student #1 in the GPA table. There is not (otherwise the FK check in step 3 would have raised an error).

So, the WHEN NOT MATCHED clause in MergeGPA attempts to INSERT a row in GPA for StudentID #1. This attempt fails (with the FK error) because StudentID #1 has already been deleted from the Student table (at step 1).

StackExchange Database Administrators Q#72169, answer score: 7