title: Check constraint using function
body: <p>I have a table called Users with these columns: UserID (int) UserName (nvarchar), Password (nvarchar), isActive (bit - true/false). </p>
  
  <p>What I want to do is to allow insert of duplicate username only if all other users with this username are disabled. </p>
  
  <p>I created this function: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>This function will return true if there's active user with this username, otherwise false. </p>
  
  <p>After that, I created check constraint based on the returned value from this function:</p>
  
  <pre><code>ALTER TABLE [dbo].[Users]  
  WITH NOCHECK ADD CHECK  (([dbo].[isActiveUsername]([UserName])=(0)))
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Help please? 
  Many thanks!!</p>