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!!

if there's active user with this username return true (1), if there's no acctive user return false (0). allow insert if there is no active user (if 0 returned) — user3125999, Oct 10 '14 at 15:46

nathan_jr · Answer 1 · 2014-10-10T16:10:38.300

3

For MSSQL 2008 and above, create a filtered index on isActive:

create unique index ux_UserName on dbo.Users(Username) where isActive = 1;

This allows you to enforce uniqueness on a subset of data.

edited Oct 10 '14 at 16:10

answered Oct 10 '14 at 15:57

nathan_jr

9,092
3
40
55

score 2 · Accepted Answer · answered Oct 10 '14 at 15:57

2

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

answered Oct 10 '14 at 15:57

DavidG

113,891
12
217
223

This is the reason why the function doesnt, er _function_, as written, but I think the filtered index is a better alternative. – nathan_jr Oct 10 '14 at 16:02
@NathanSkerl I agree, but didn't see that it was SQL2012 till just now. This method will work in SQL2005. – DavidG Oct 10 '14 at 16:05
@DavidG Very helpful answer. would you know by chance the reason why it works this way? It does not sound logical to me, I would have expected that the function runs first, and the insert only applies if the function result is considered safe. – A.D. Jun 23 '22 at 08:16

Check constraint using function

2 Answers2