logo标签列表

为什么这个更新操作会因为唯一键约束冲突而失败？

sql-serverupdateviewconstraintunique-constraint
我是一个“偶然”的数据库管理员，经验相对不足，对这个问题感到困惑。 运行的是 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'
应该只更新由vReclaimable视图返回的tAccts表中的行。 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
请展示给我们CREATE VIEW语句。 - ypercubeᵀᴹ
OHR_EmpStatus是表格、视图还是两者都有的列？ - ypercubeᵀᴹ
1我简直惊讶于对我的问题的快速和优秀的回应。就像我所说的，这可能是我忽略或误解的一些显而易见的事情，但我不认为这是因为我最终得到了交叉连接。我应该发布tAccts表和vReclaimable视图的定义。我会审查到目前为止发布的所有答案，如果我仍然认为它们没有准确地命中目标，我会在问题中添加那些定义。同时，非常感谢每个回应的人。 - ArleyD
根据ypercube的要求，添加tAccts和vReclaimable的CREATE语句。 - ArleyD
5个回答

归根结底，这取决于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' ;
由于在FROM子句中没有提及dbo.tAccts表，并且表和视图之间也没有连接或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'
这里必须有一些东西来匹配表之间的行，比如 tAccts.id = vReclaimable.id。
另一种表达方式是： 问题在于你相信这个语句“应该只更新由vReclaimable视图返回的tAccts表中的行”。 事实并非如此。它会更新与UPDATE之后提到的表tAccts中的OHR_EmpStatus <> 'A'条件匹配的所有行（即WHERE中的条件）。它可能会在此过程中使用vReclaimable的数据（但你对此没有任何引用）。 如果你想要将其限制为仅包含在vReclaimable中的行，除了其他已提供的选项外，你可以使用子查询： 
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)
WHERE OHR_EmpStatus <> 'A' AND tAccts.key IN (SELECT key FROM vReclaimable)
如果下面的查询返回多行： 
select 'tAccts table', dnis, method, destination, phone_tf, phone_local 
from tAccts
where OHR_EmpStatus <> 'A'
你试图使用相同的值更新多行数据，从而违反了唯一约束。
也许值得研究一下这个链接：http://stackoverflow.com/questions/2648445/if-i-update-a-view-will-my-original-tables-get-updated - infiniteLoop
另一个选项是： 你不需要

从 dbo.vReclaimable

因为你在更新语句中没有使用这个表的任何值。

就记录而言：在这种情况下，与vReclaimable的连接无疑是为了过滤正在更新的表。虽然对于SET子句来说并不需要它，但它实际上是WHERE子句的一部分。 - Jon of All Trades