Problem when trying to convert CHECK with subqueries to a function in SQL

Question

I've been sitting with this problem for hours and I'm on the verge of going crazy. I want each Student to only be able to study a maximum of 45 courseCredits. At this point it prohibits me from adding more than a total of 45 CourseCredits. How am I supposed to rearrange the Function CheckCredits?

CREATE TABLE Student
(
    studentID VARCHAR(5),
    studentName VARCHAR(20),
    studentAddress VARCHAR(20),
    CONSTRAINT student_pk PRIMARY KEY(studentID)
)

CREATE TABLE Course
(
    courseID VARCHAR(5),
    courseCredits INT,
    courseName VARCHAR(20),
    CONSTRAINT course_pk PRIMARY KEY(courseID)
)

CREATE TABLE Studies
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    CONSTRAINT studies_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT studies_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT studies_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

CREATE TABLE HasStudied
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    grade VARCHAR(1),
    CONSTRAINT has_studied_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT has_studied_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT has_studied_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

GO
CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
   DECLARE @returnvalue INT
   SELECT @returnvalue = (SELECT SUM(courseCredits) FROM Course c where c.courseID IN(
    SELECT s.courseID FROM Studies s JOIN Student st ON s.studentID = st.studentID OR c.courseID = s.courseID))
   RETURN @returnvalue
END;
GO

ALTER TABLE Studies 
ADD CONSTRAINT chkCredits CHECK (dbo.CheckCredits() <= 45);  



INSERT INTO Student VALUES('S1', 'Joe', 'Street')
INSERT INTO Student VALUES('S2', 'Joe', 'Street')
INSERT INTO Student VALUES('S3', 'Joe', 'Street')
INSERT INTO Student VALUES('S4', 'Joe', 'Street')

INSERT INTO Course VALUES('C1', 45, 'Biology')
INSERT INTO Course VALUES('C2', 15, 'History')
INSERT INTO Course VALUES('C3', 35, 'English')
INSERT INTO Course VALUES('C4', 20, 'Music')

INSERT INTO Studies VALUES('S1', 'C1')
INSERT INTO Studies VALUES('S2', 'C2')
INSERT INTO Studies VALUES('S3', 'C3')

Now I can't add S2 to C2 or S3 to C3, because S1 studies C1 which have 45 points. So it does not consider the students as separate. — Axel Görnebrand, Sep 17 '20 at 15:28
The check function is being applied to all students and courses. Since it returns the sum of `courseCredits` for all of the courses which _any_ student has registered for it is likely to be >45. You would probably do better with a `trigger` that checks only the rows being inserted or updated in the appropriate tables. Note that triggers fire once per _statement_, not _row_. — HABO, Sep 17 '20 at 16:51
Thank you for your reply Habo. I'm having a hard time understanding triggers. Is there no way to do it with the function? — Axel Görnebrand, Sep 17 '20 at 17:25
It can be done with a function, but it has no access to the specific row(s) being updated. Hence, it has to check the course credits for every student. Start by writing a query that returns the total course credits for each student. Then your check becomes something like `case when exists ( select Sum( courseCredits ) as totalCredits from ... group by studentId having totalCredits > 45 ) then 1 else 0 end`. Not very efficient as the number of students increases. And courses. And years of data. — HABO, Sep 17 '20 at 17:32
Don't forget the _other_ checks. If a course is updated to have `courseCredits` set to `50` then what happens? Even changing one from `15` to `20` might cause a student to violate your rule, but it won't be detected until some unrelated activity in the `Studies` table causes an unexpected constraint violation. — HABO, Sep 17 '20 at 20:00

score 0 · Accepted Answer · answered Sep 17 '20 at 17:44

0

Change your function to return the max credits per student. Like this:

CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
    DECLARE @returnvalue INT
    SELECT TOP 1 @returnvalue = SUM(courseCredits) 
    FROM Studies s 
    JOIN Course c ON s.courseID = c.courseID 
    GROUP BY s.studentID 
    ORDER BY SUM(courseCredits)  desc 

   RETURN @returnvalue
END;
GO

answered Sep 17 '20 at 17:44

Wouter

2,881
2
9
22

Thank you very much! This works splendid. Now let me just try to understand it: it selects the student with the highest sum of coursecredit, inserts that into @returnvalue. This together with ALTER TABLE Studies ADD CONSTRAINT chk_Credits CHECK (dbo.CheckCredits() <= 45); prohibits it from being more than 45? – Axel Görnebrand Sep 17 '20 at 18:31

Problem when trying to convert CHECK with subqueries to a function in SQL

1 Answers1