How to limit adding a row to a table in MS SQL DB by attribute?

Question

SQL

There is a table for accounting for issued books in the library - columns id_student and id_books. It is necessary to implement the restriction "a student must not have more than seven books on hand", how to do this? I didn’t google anything sensible, I myself think that when adding a line, there should be a request for the number of books issued and a comparison with the number 7, but I don’t know how to implement this.

Answer 1

How to limit adding a row to a table in MS SQL DB?

From help:

CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

It is clear that you need to pass the student_id to the check function and use it in the request.

Answer 2

if works in sql queries, if there is a number of issued books - count and if to help. I do not give a solution because it will not be interesting.