Adding A Constraint To Prevent Duplicates In Sql Update Trigger

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 wi

Solution 1:

You can add a unique contraint on the table, this will raise an error if you try and insert or update and create duplicates

ALTERTABLE [Users] ADDCONSTRAINT [IX_UniqueUserEmail] UNIQUE NONCLUSTERED 
(
    [Email] ASC
)

ALTERTABLE [Users] ADDCONSTRAINT [IX_UniqueUserName] UNIQUE NONCLUSTERED 
(
    [UserName] ASC
)

EDIT: Ok, i've just read your comments to another post and seen that you're using NVARCHAR(MAX) as your data type. Is there a reason why you might want more than 4000 characters for an email address or username? This is where your problem lies. If you reduce this to NVARCHAR(250) or thereabouts then you can use a unique index.

Solution 2:

Sounds like a lot of work instead of just using one or more unique indexes. Is there a reason you haven't gone the index route?

Solution 3:

Why not just use the UNIQUE attribute on the column in your database? Setting that will make the SQL server enforce that and throw an error if you try to insert a dupe.

Solution 4:

You should use a SQL UNIQUE constraint on each of these columns for that.

Solution 5:

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?