在 SQL 更新期间违反 UNIQUE KEY 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7249642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Violation of UNIQUE KEY constraint during SQL update
提问by Bryan
I have a simple database table (SQL Server 2008 R2 Express), which has a definition as follows:
我有一个简单的数据库表（SQL Server 2008 R2 Express），它的定义如下：
ID INT Auto Inc, PK
Name VARCHAR(64) Unique Key
Telephone VARCHAR(128)
I have a stored procedure which I execute to update records within the table which basically does the following:
我有一个存储过程，我执行它来更新表中的记录，它基本上执行以下操作：
UPDATE customers
SET Name = @name, Telephone = @Telephone
WHERE id = @id
Currently, I have two entries in the table
目前，我在表中有两个条目
ID Name Telephone
1 Fred 01234 567890
2 John 09876 543210
When I call my stored procedure to update the telephone number for John, the SQL that is effectively executed is
当我调用我的存储过程来更新 John 的电话号码时，有效执行的 SQL 是
UPDATE customers
SET Name = 'John', Telephone = '02468 135790'
WHERE id = 2
This generates a UNIQUE KEY violation on the Namefield. Now as the Name field doesn't actually change, why does this occur?
这会在该Name字段上生成 UNIQUE KEY 违规。现在，由于 Name 字段实际上并没有改变，为什么会发生这种情况？
As all database actions are being handled by my app using stored procedures, I could fix this by removing the constraint, and modifying the stored procedures to manually enforce the constraint, but this just seems wrong.
由于我的应用程序使用存储过程处理所有数据库操作，因此我可以通过删除约束并修改存储过程以手动强制执行约束来解决此问题，但这似乎是错误的。
Given that my table actually has many more fields, there must be a generic work around that I can employ to prevent these false constraint problems, without having to generate numerous stored procedures to update specific fields?
鉴于我的表实际上有更多的字段，我必须有一个通用的工作来防止这些错误的约束问题，而不必生成大量的存储过程来更新特定的字段？
Edit: The above table was simplified to keep the question more manageable, I'm pretty sure I've not missed anything important, but for info, the actual definition of the table is as follows
编辑：上表已简化以使问题更易于管理，我很确定我没有遗漏任何重要的信息，但对于信息，该表的实际定义如下
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
...and the stored procedure
...和存储过程
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
回答by Martin Smith
You're missing the WHEREin your UPDATEstatement so currently it will try and update all rows in the table with the same values.
您WHERE的UPDATE语句中缺少 ，因此目前它将尝试使用相同的值更新表中的所有行。
