CODEWITHSUNDEEP
sqlsql-server
Lopfest
Lopfest

Reputation: 113

MSSQL function in check constraint

I would like to create table with CHECK constraint, where CHECK calls an user defined scalar function. I have read on multiple sites that it is possible, also that it has bad performance. Even though I would like to do it.

I have this table

CREATE TABLE [book_history] (
    id int NOT NULL IDENTITY PRIMARY KEY,
    user_id int NOT NULL,
    library_id int NOT NULL,
    book_id int NOT NULL,
    borrow_time datetime DEFAULT GETDATE(),
    return_policy datetime DEFAULT DATEADD(DAY, 30, GETDATE()),
    return_time datetime,
    CHECK (dbo.fn_check_duplicate(user_id, library_id, book_id) = 0)
);

and function

DROP FUNCTION IF EXISTS fn_check_duplicate
GO
CREATE FUNCTION fn_check_duplicate (@user_id int, @library_id int, @book_id int)
RETURNS int
BEGIN
    RETURN (SELECT COUNT(*) FROM [book_history] WHERE user_id = @user_id AND library_id = @library_id AND book_id = @book_id AND return_time IS NULL)
END
GO

When I try to insert new row into this book_history table (which is empty), I get an error saying The INSERT statement conflicted with the CHECK constraint "CK__book_history__267ABA7A". The conflict occurred in database "library", table "dbo.book_history".

COUNT is supposed to return int data type based on MSDN documentation.
I am owner of both, the table and the function.

Can anyone tell me what am I doing wrong?

Upvotes: 1

Views: 135

Answers (1)

SqlZim
SqlZim

Reputation: 38043

Change it to check (dbo.fn_check_duplicate(user_id, library_id, book_id) = 1)

The check is going to look at the state of the table after the insert, so you want the count to be 1.

Test it on rextester: http://rextester.com/AWDNP40594 by uncommenting the second insert.

You can also replace this slow check constraint with a filtered unique index like so:

create unique nonclustered index uix_book_history_user_library_book
  on dbo.book_history (user_id, library_id, book_id)
    where return_time is null

This might be more of what you are trying to do, if each book_id is an individual book:

create unique nonclustered index uix_book_history_library_book
  on dbo.book_history (library_id, book_id)
    where return_time is null

Because this would allow a book to only be checked out by one user at a time.

Upvotes: 4

Related Questions