Check constraint using function

Question

I have a table called Users with these columns: UserID (int) UserName (nvarchar), Password (nvarchar), isActive (bit - true/false).

What I want to do is to allow insert of duplicate username only if all other users with this username are disabled.

I created this function:

ALTER FUNCTION [dbo].[isActiveUsername]
    (@username nvarchar(15))
RETURNS bit
AS

BEGIN

IF EXISTS
            (
                SELECT * 
                FROM Users
                WHERE isActive = 1 AND UserName = @username
            )
            RETURN 1
    RETURN 0
END

This function will return true if there's active user with this username, otherwise false.

After that, I created check constraint based on the returned value from this function:

ALTER TABLE [dbo].[Users]  
WITH NOCHECK ADD CHECK  (([dbo].[isActiveUsername]([UserName])=(0)))

If there's no active user with this username, allow insert... But for some reason when I try to insert a new user, no metter what username I try, I get this error:

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__Users__UserName__4D94879B". The conflict occurred in database "Chat", table "dbo.Users", column 'UserName'.
The statement has been terminated.

Help please? Many thanks!!

Answer 1

The problem is that the INSERT happens BEFORE your CHECK constraint runs, so there is always an user in the table before the function runs. Change your function to this:

ALTER FUNCTION [dbo].[isActiveUsername]
    (@username nvarchar(15))
RETURNS bit
AS
BEGIN
    IF  (SELECT COUNT(*) FROM Users WHERE isActive = 1 AND UserName = @username) > 1
    BEGIN
        RETURN 1
    END

    RETURN 0

END