Adding a constraint to prevent duplicates in SQL U

Question

We have a user table, every user has an unique email and username. We try to do this within our code but we want to be sure users are never inserted (or updated) in the database with the same username of email. I've added a BEFORE INSERT Trigger which prevents the insertion of duplicate users.

CREATE TRIGGER [dbo].[BeforeUpdateUser]
   ON  [dbo].[Users]
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Email nvarchar(MAX)
    DECLARE @UserName nvarchar(MAX)
    DECLARE @UserId int
    DECLARE @DoInsert bit

    SET @DoInsert = 1

    SELECT @Email = Email, @UserName = UserName FROM INSERTED

    SELECT @UserId = UserId FROM Users WHERE Email = @Email

    IF (@UserId IS NOT NULL)
        BEGIN
            SET @DoInsert = 0
        END

    SELECT @UserId = UserId FROM Users WHERE UserName = @UserName

    IF (@UserId IS NOT NULL)
        BEGIN
            SET @DoInsert = 0
        END

    IF (@DoInsert = 1)
        BEGIN
            INSERT INTO Users
            SELECT 
                           FirstName, 
                           LastName, 
                           Email, 
                           Password, 
                           UserName, 
                           LanguageId,
                           Data, 
                           IsDeleted 
                       FROM INSERTED
        END
    ELSE
        BEGIN
            DECLARE @ErrorMessage nvarchar(MAX)
            SET @ErrorMessage = 
                         'The username and emailadress of a user must be unique!'
            RAISERROR 50001 @ErrorMessage
        END 
END

But for the Update trigger I have no Idea how to do this. I've found this example with google: http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/ But I don't know if it applies when you update multiple columns at once.

EDIT:

I've tried to add a unique constraint on these columns but it doesn't work:

Msg 1919, Level 16, State 1, Line 1
Column 'Email' in table 'Users' is of a type 
that is invalid for use as a key column in an index.

Answer 1

You can create a UNIQUE INDEX on an NVARCHAR as soon as it's an NVARCHAR(450) or less.

Do you really need a UNIQUE column to be so large?