Are SQL Server unique constraints “Silent” or do they raise an exception?

I want to prevent inserting duplicate values into my tables. At first, I added code to check if the value already existed in the database, but that seems like a lot of overhead / wasted time if I can prevent it at the DDL level.

So I found this and changed one of my tables (as an example) from this:

CREATE TABLE [dbo].[ACTORS] 
(
    [Id]      INT IDENTITY (1, 1) NOT NULL,
    [ActorId] CHAR(9)     NOT NULL,
    [Actor]   VARCHAR(50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),
);

to this:

CREATE TABLE [dbo].[ACTORS] 
(
    [Id]      INT IDENTITY (1, 1) NOT NULL,
    [ActorId] CHAR(9)     NOT NULL,
    [Actor]   VARCHAR(50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [CK_ACTORS_Column] 
        UNIQUE NONCLUSTERED ([ActorId] ASC)
);

I want the constraint to prevent a second identical ActorId without whin[g]ing about it. IOW, just bypass it, don’t tell me about it, don’t stop the app or throw an exception.

Is this how it works (silently), or will it throw an exception?

Answer

Let’s try that:

insert into actors (actorid,actor) values('foo', 'bar');
-- 1 row affected

insert into actors (actorid, actor) values('foo', 'baz');
-- Msg 2627 Level 14 State 1 Line 1
-- Violation of UNIQUE KEY constraint 'CK_ACTORS_Column'. 
-- Cannot insert duplicate key in object 'dbo.ACTORS'. The duplicate key value is (foo      ).

A unique constraint violation does raise an error. This is how the database lets you know that something went wrong.

SQL Server has built-in no option (that I know about) to ignore such error, unlike many other databases (MySQL, Postgres, SQLite…). A workaround is to rewrite the insert with not exists and a subquery:

insert into actors (actorid, actor)
select v.*
from (values ('foo', 'bar')) v(actorid, actor)
where not exists (select 1 from actor a where a.actorid = v.actorid)

Another option is the merge statement:

merge into actors a
using (values ('foo', 'bar')) v(actorid, actor)
on v.actorid = a.actorid
when not matched then insert (actorid, actor)
values (v.actorid, v.actor)