CODEWITHSUNDEEP
sql
Sara Popa
Sara Popa

Reputation: 163

sql: Error in creating a constraint which calls a function

I am trying to make a constraint which restricts a student to loan a book if he doesn't have a subscription to that library.

LibraryBooks: this table contains the books that you can find in a library.

idLibrary  |  idBook
1          |  1
1          |  2
1          |  3 
2          |  1

StudentLibrary: table where each student has a subscription to a certain library.

idStudent  |  idLibrary
1          |  1
2          |  1

LoanBook: shows what books were loan, by whom and from where. 

idStudent  |  idBook     |  idLibrary  
1          |  1          |  1
1          |  2          |  1
1          |  3          |  1
2          |  1          |  2

For example, student with idStudent = 1 cannot loan a book from the library with idLibrary = 2.

This is my code:

CREATE FUNCTION dbo.CheckFunction(@x int, @y int)
returns int
as begin
    declare @ret int;
    if (select StudentLibrary.idLibrary from StudentLibrary 
                      where @x = StudentLibrary.idStudent and @y = StudentLibrary.idLibrary) != null
        SET @ret = @y;
    else
        SET @ret = 0;  

    return @ret;
end
go

ALTER TABLE LoanBook
ADD CONSTRAINT constraint_student_library
CHECK (LoanBook.idLibrary = dbo.CheckFunction(LoanBook.idStudent, LoanBook.idLibrary))

I have the following error:

The ALTER TABLE statement conflicted with the CHECK constraint "constrangere_student_biblioteca". The conflict occurred in database "DataBaseLibraries", table "dbo.LoanBook".

Upvotes: 2

Views: 94

Answers (1)

Ali Al-Mosawi
Ali Al-Mosawi

Reputation: 778

check the below:-

CREATE FUNCTION dbo.CheckStudentCanLoan (@idStudent int, @idLibrary int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
       -- find if idStuded is registered with this idLibrary
    SELECT @retval = count(*) FROM StudentLibrary
        WHERE idStudent=@idStudent and idLibrary=@idLibrary
  RETURN @retval
END;

ALTER TABLE LoanBook 
  ADD CONSTRAINT CheckStudentCanLoanCon 
  CHECK (dbo.CheckStudentCanLoan(idStudent, idStudent) >0);

The check can be equal to 1 but more than zero will work, if the user had a duplicate entry.

I used the following to test

create table LibraryBooks (idLibrary   int,idBook int)
insert into LibraryBooks values (1,1),(1,2),(1,3),(2,1)
create table StudentLibrary (idStudent     int,idLibrary int)
insert into StudentLibrary values (1,1),(2,1)
create table LoanBook (idStudent int,idBook int,idLibrary  int)

This works

insert into LoanBook values (1,1,1),(1,2,1),(1,3,1)

This hits the constrain

insert into LoanBook values (1,1,1),(1,2,1),(1,3,1),(2,1,2),(1,1,2)

with the expected error Msg 547, Level 16, State 0, Line 8 The INSERT statement conflicted with the CHECK constraint "CheckStudentCanLoanCon". The conflict occurred in database "...\DATABASE1.MDF", table "dbo.LoanBook", column 'idStudent'. The statement has been terminated.

and if we wanted to tuned yours to work, this can be a solution:-

CREATE FUNCTION dbo.CheckFunction(@x int, @y int)
returns int
as begin
declare @ret int;
if exists(select StudentLibrary.idLibrary from StudentLibrary 
                  where @x = StudentLibrary.idStudent and @y = StudentLibrary.idLibrary) 
    SET @ret = @y;
else
    SET @ret = 0;  

return @ret;

end

go

ALTER TABLE LoanBook
ADD CONSTRAINT constraint_student_library
CHECK (LoanBook.idLibrary = dbo.CheckFunction(LoanBook.idStudent, LoanBook.idLibrary))

Upvotes: 2

Related Questions