Just a quick reminder that there is no point manually checking database constraints before modifying your data. Take this code for example:

create table Users (
    UserID uniqueidentifier
        primary key not null default newid(),

    LoginName nvarchar(50) not null unique
)

if not exists (
    select * from Users
    where LoginName = 'JohnSmith'
)
begin
    insert into Users (LoginName)
    values ('JohnSmith')
end

There is a race condition between the if not exists check and the actual insertion of data. Another connection could insert the same data before this insert gets a chance to complete. We could wrap the check and insert in a transaction to ensure consistency as follows (note the isolation level because we need to place a range lock on data that could be there):

begin tran
set transaction isolation level repeatable read
if not exists (
    select * from Users
    where LoginName = 'JohnSmith'
)
begin
    insert into Users (LoginName)
    values ('JohnSmith')
end
commit

This works but we’ve got the added expense of a transaction. There is no point in doing this though as there is already a unique constraint on the column and databases are very good at checking constraints. With this in mind, the new and improved version looks like:

insert into Users (LoginName) 
values ('JohnSmith')

Much simpler. There is only one minor problem, for which I don’t have an answer, and that is if you have multiple constraints on a table then it’s very difficult to determine which one failed. The @@error variable will be set to 547 (constraint violation) and a suitable raiserror is generated which could be parsed by the caller but not easily.