CODEWITHSUNDEEP
sql-serverconstraintssql-functioncheck-constraint
user3125999
user3125999

Reputation: 201

Check constraint using function

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

Upvotes: 1

Views: 3401

Answers (2)

nathan_jr
nathan_jr

Reputation: 9292

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.

Upvotes: 3

DavidG
DavidG

Reputation: 119017

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

Upvotes: 3

Related Questions