SQL 更新期间违反 UNIQUE KEY 约束

Question

我有一个简单的数据库表（SQL Server 2008 R2 Express），其定义如下：

ID         INT          Auto Inc, PK
Name       VARCHAR(64)  Unique Key
Telephone  VARCHAR(128)

我有一个存储过程，我执行该存储过程来更新表中的记录，该过程基本上执行以下操作：

UPDATE customers
SET    Name = @name, Telephone = @Telephone
WHERE  id = @id

目前，我在表中有两个条目

ID   Name    Telephone
1    Fred    01234 567890
2    John    09876 543210

当我调用存储过程来更新 John 的电话号码时，有效执行的 SQL 是

UPDATE customers
SET    Name = 'John', Telephone = '02468 135790'
WHERE  id = 2

这会在 Name 字段上生成 UNIQUE KEY 冲突。既然名称字段实际上没有改变，为什么会发生这种情况呢？

由于所有数据库操作都是由我的应用程序使用存储过程处理的，因此我可以通过删除约束并修改存储过程以手动强制执行约束来解决此问题，但这似乎是错误的。

鉴于我的表实际上有更多的字段，必须有一个通用的解决方法，我可以使用它来防止这些错误约束问题，而不必生成大量存储过程来更新特定字段？

编辑：上表经过简化，以使问题更易于管理，我很确定我没有错过任何重要的内容，但作为信息，该表的实际定义如下

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[companies](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [typeId] [int] NOT NULL,
    [name] [varchar](64) NOT NULL,
    [displayName] [varchar](128) NOT NULL,
    [deliveryAddress] [varchar](1024) NOT NULL,
    [invoiceAddress] [varchar](1024) NOT NULL,
    [telephone] [varchar](64) NOT NULL,
    [fax] [varchar](64) NOT NULL,
    [email] [varchar](256) NOT NULL,
    [website] [varchar](256) NULL,
    [isActive] [bit] NOT NULL,
 CONSTRAINT [PK_companies] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Unique Display Name] UNIQUE NONCLUSTERED 
(
    [displayName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Unique Name] UNIQUE NONCLUSTERED 
(
    [name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[companies]  WITH CHECK ADD  CONSTRAINT [Company Type] FOREIGN KEY([id])
REFERENCES [dbo].[companyTypes] ([id])
GO

ALTER TABLE [dbo].[companies] CHECK CONSTRAINT [Company Type]
GO

......和存储过程

ALTER PROCEDURE UpdateCompany

    @id                 INT,
    @typeId             INT,
    @name               VARCHAR(64),
    @displayName        VARCHAR(128),
    @deliveryAddress    VARCHAR(1024),
    @invoiceAddress     VARCHAR(1024),
    @telephone          VARCHAR(64),
    @fax                VARCHAR(64),
    @email              VARCHAR(256),
    @website            VARCHAR(256),
    @isActive           BIT

AS
BEGIN

    UPDATE  companies
    SET     typeid = @typeid,
            name = @name,
            displayname = @displayname,
            deliveryAddress = @deliveryAddress,
            invoiceAddress = @invoiceAddress,
            telephone = @telephone,
            fax = @fax,
            email = @email,
            website = @website,
            isActive = @isActive

    EXEC    GetCompany @id


END
GO

Answer 1

您的 UPDATE 语句中缺少 WHERE，因此目前它将尝试使用相同的值更新表中的所有行。