代码之家 › 专栏 › 技术社区 › B. Clay Shannon-B. Crow Raven

SQL Server唯一约束是“静默”还是引发异常?

unique-constraint sql-insert tsql sql-server sql

0

B. Clay Shannon-B. Crow Raven · 技术社区 · 5 年前

所以我发现 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),
);

为此:

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)
);

我要用约束来防止第二个相同的 ActorId

1 回复 | 直到 5 年前

1

GMB 5 年前

我们试试看:

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      ).

与许多其他数据库(MySQL、Postgres、SQLite等)不同,sqlserver内置了no选项来忽略此类错误。解决方法是重写 insert not exists 以及子查询:

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)

另一个选择是 merge 声明:

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)