Sql-Server
為什麼此 UPDATE 因違反唯一鍵約束而失敗？
我是一個“偶然的”DBA，相對缺乏經驗並且對這個問題感到困惑。
執行 MS SQL Server 2012。問題出在這條 UPDATE 語句上：
UPDATE dbo.tAccts SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) FROM dbo.vReclaimable WHERE OHR_EmpStatus <> 'A'應該**只更新tAccts表中由 vReclaimable 視圖返回的行。
vReclaimable 視圖基於 tAccts 表並返回 tAccts 中行的子集。
當我執行它時，它會因唯一鍵錯誤而失敗：
(0 row(s) affected) Msg 2627, Level 14, State 1, Line 67 Violation of UNIQUE KEY constraint 'UQ__tAccounts_DNIS.Method.Destination.Phones'. Cannot insert duplicate key in object 'dbo.tAccts'. The duplicate key value is (68497, smtp, r00417819@mail.ad.ge.com, 800-905-8793, none). The statement has been terminated.很公平，tAccts 表確實有一個唯一的鍵約束：
CONSTRAINT [UQ__tAccounts_DNIS.Method.Destination.Phones] UNIQUE NONCLUSTERED ( [DNIS] ASC,[Method] ASC,[Destination] ASC,[Phone_TF] ASC,[Phone_Local] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)但奇怪的是。如果我執行這兩個查詢：
select 'tAccts table', dnis, method, destination, phone_tf, phone_local from tAccts where dnis=68497 select 'vReclaimable view', dnis, method, destination, phone_tf, phone_local, daysidle from vReclaimable where dnis=68497第一個返回兩行（如預期的那樣）：
(No column name) dnis method destination phone_tf phone_local tAccts table 68497 ftp ftp://faxuser@ap1plm02cige/appliances 800-905-8793 none tAccts table 68497 unc \\\\for4as01applge\\cfs_portfolio\\cfs_faxdocs 800-905-8793 none第二個返回 0 行（如預期的那樣）。
如果“FROM vReclaimable WHERE OHR_EmpStatus <> ‘A’”返回 0 行，為什麼 UPDATE 嘗試更新 DNIS=68497 的行？
（我希望我已經充分描述了這一點。我覺得我錯過了一些明顯的東西）
USE [TEST-GEAFax_arley_NEW] GO /****** Object: Table [dbo].[tAccts] Script Date: 12/9/2015 1:39:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tAccts]( [Ticket] [varchar](30) NOT NULL, [Method] [varchar](15) NOT NULL, [AcctOwner] [varchar](15) NOT NULL, [DisplayName] [varchar](75) NOT NULL, [Destination] [varchar](75) NOT NULL, [DNIS] [varchar](20) NOT NULL, [DNIS2] [varchar](20) NULL, [Phone_TF] [varchar](30) NOT NULL, [Phone_Local] [varchar](30) NOT NULL, [Phone_PBX] [varchar](255) NOT NULL, [UpdatedBy] [varchar](50) NOT NULL, [UpdatedOn] [date] NOT NULL, [FaxNotes] [varchar](255) NULL, [TelcomNotes] [varchar](255) NULL, [AcctID] [int] IDENTITY(0,1) NOT NULL, CONSTRAINT [PK__tAccounts_AcctID] PRIMARY KEY CLUSTERED ( [AcctID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UQ__tAccounts_DNIS.Method.Destination.Phones] UNIQUE NONCLUSTERED ( [DNIS] ASC, [Method] ASC, [Destination] ASC, [Phone_TF] ASC, [Phone_Local] 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 --------------------------------------------------------------------------------- USE [TEST-GEAFax_arley_NEW] GO /****** Object: View [dbo].[vReclaimable] Script Date: 12/9/2015 1:39:57 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*********************************************************************** * Written By : N. Arley Dealey (200018252 * Written On : * Updated By : * Updated On : * Description : Returns data from tAccts, vRxAl, vWLT_AllGE * Notes : ***********************************************************************/ CREATE VIEW [dbo].[vReclaimable] AS SELECT a.Ticket , a.Method , a.AcctOwner , a.DisplayName , a.Destination , a.DNIS , a.DNIS2 , a.Phone_TF , a.Phone_Local , a.Phone_PBX , a.UpdatedBy , a.UpdatedOn , a.FaxNotes , a.TelcomNotes , a.AcctID , COUNT(jt.JobID) AS 'FaxesRcvd' , CAST(MIN(jt.TimeStamp_UTC) AS DATE) AS 'FirstRcvd' , CAST(MAX(jt.TimeStamp_UTC) AS DATE) AS 'LastRcvd' , DATEDIFF(dd, MAX(jt.TimeStamp_UTC), GETDATE()) AS 'DaysIdle' , o.OHR_EmpSSO , o.OHR_EmpStatus , o.OHR_EmpName , o.OHR_EmpTitle , o.OHR_BizIndustryGroup , o.OHR_BizSegment , o.OHR_BizUnit , o.OHR_BizDept , o.OHR_BizDomain FROM dbo.tAccts AS a LEFT OUTER JOIN dbo.tAccts_Retain AS r ON (a.AcctID = r.AcctID) LEFT OUTER JOIN dbo.vWLT_AllGE AS o ON (a.AcctOwner = o.OHR_EmpSSO) LEFT OUTER JOIN dbo.vRxAll AS jt ON (a.DNIS = jt.DNIS) WHERE ( 1 -- place holder, has no effect AND r.RetainID IS NULL -- out of scope: in Retain table AND a.Method = 'smtp' -- out of scope: ftp, unc, cifs, printers AND a.Phone_Local NOT LIKE '216-%' -- out of scope: NELA numbers AND a.AcctOwner <> 'r00417819' -- out of scope: reclaimed numbers AND a.AcctOwner <> 'r00336832' -- out of scope: never assigned numbers AND a.AcctOwner <> 'r00971729' -- out of scope: invalid numbers AND a.Destination NOT LIKE 'g%@mail.ad.ge.com' -- out of scope: distribution lists AND a.Destination NOT LIKE 'r%@mail.ad.ge.com' -- out of scope: shared mailboxes ) GROUP BY a.DNIS -- remaining columns are just for syntax reasons , a.Ticket, a.Method, a.AcctOwner, a.DisplayName, a.Destination, a.DNIS2, a.Phone_TF, a.Phone_Local, a.Phone_PBX, a.UpdatedBy, a.UpdatedOn, a.FaxNotes, a.TelcomNotes, a.AcctID , o.OHR_EmpSSO, o.OHR_EmpStatus, o.OHR_EmpName, o.OHR_EmpTitle , o.OHR_BizIndustryGroup, o.OHR_BizSegment, o.OHR_BizUnit, o.OHR_BizDept, o.OHR_BizDomain GO
它歸結為
UPDATE聲明的作用。這並不完全明顯，但你的陳述等同於這個：UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) FROM dbo.tAccts AS upd CROSS JOIN dbo.vReclaimable AS v WHERE OHR_EmpStatus <> 'A' ;由於在表和視圖之間沒有提及
dbo.tAccts表FROM並且沒有連接或 where 條件，因此它會導致CROSS連接並嘗試更新表的所有行（而不僅僅是來自視圖），並且可能是多個次也是！您可以使用以下方法添加連接（或位置）條件：
UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) FROM dbo.tAccts AS upd JOIN dbo.vReclaimable AS v ON v.PK = upd.PK -- whatever the PK column is WHERE OHR_EmpStatus <> 'A' ;或（使用您的版本）：
UPDATE dbo.tAccts SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) FROM dbo.vReclaimable WHERE OHR_EmpStatus <> 'A' AND vReclaimable.PK = tAccts.PK ;或者，您可以（可能）簡單地更新視圖。為此，視圖必須符合*“可更新視圖”*的限制。請參閱 MSDN 文件中的相關段落：
CREATE VIEW，可更新視圖：UPDATE dbo.vReclaimable SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) WHERE OHR_EmpStatus <> 'A' ;
您的更新查詢中的表之間似乎沒有連接。
UPDATE dbo.tAccts SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' , UpdatedBy = SYSTEM_USER , UpdatedOn = CAST(GetDate() AS DATE) FROM dbo.vReclaimable WHERE OHR_EmpStatus <> 'A'這裡必須有一些東西來匹配表之間的行，例如 where tAccts.id = vReclaimable.id