Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have identified 3 situations.

  1. A student with no enrollments.
  2. A student with enrollments but no grades.
  3. 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:

  1. Visual Studio 2013 Professional.
  2. IIS express (internal to VS2013).
  3. ASP.NET Web App using EntityFramework 6.1.1.
  4. MS SQL Server 2014 Enterprise.
  5. GPA.Value is nullable.
  6. Enrollment.GradeID is nullable.

Here is a snippet of the database:

database image

--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.
share|improve this question

2 Answers 2

up vote 6 down vote accepted

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

  1. The row is deleted from the Student table
  2. The cascade delete removes corresponding rows from Enrollment
  3. The foreign key relationship GPA -> Student is checked
  4. 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).

share|improve this answer
1  
I think you're on to something. When a student is created with enrollments, but no grades have been assigned, that student has no entry in the GPA table. When the database goes to delete that student it looks at the database, sees enrollments to delete but no GPA entry. So it sets about deleting the enrollments, which cause a trigger to fire which creates the GPA entry, which then causes the constraint violation? So the solution is to create a GPA entry when I create a student. Then my insert trigger won't need a conditional, and my stored procedure won't need to be a merge, just an update. –  DowntownHippie Jul 24 at 17:56

Without reading all, just from the diagram: You have either an entry in Enrollment or one in GPA pointing to the Student you want to delete.

The entries with the foreign keys need to be deleted first (or the keys set to null, but that is bad practice) before you can delete the Student entry.

Also some databases have ON DELETE CASCADE, which will delete any entries with foreign keys to the one you want to delete.

Another way is to not declare them as foreign keys and only use the key value, but that's not reommended either.

share|improve this answer
    
In the cases where it is failing there is an entry in Enrollment but not one in GPA. –  DowntownHippie Jul 23 at 23:12
    
you have some constraints with ON DELETE CASCADE and some without. try adding that line to all constraints. after that would try disabling all triggers and after that test with a minimal setup. good luck –  user44286 Jul 23 at 23:39
    
I see those ON DELETE CASCADE statements. None of these table creation statements, nor the delete statements are hand written, they are all generated by the entityframework. The cascades are because enrollment has foreign keys that are not it's primary key; GPA's foreign key constraint is it's primary key so it shouldn't need a cascade. I've tested this, if you delete a student with a GPA table entry the entry gets deleted. The only issue is a student with enrollments but no gpa. –  DowntownHippie Jul 24 at 1:03

protected by Community Jul 24 at 15:29

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.