使用SELECT和NOT EXISTS检查INSERT时出现唯一索引错误

Question

如果我正在运行存储过程，我会得到以下错误

不能在对象' dbo '中插入重复的键行。具有唯一索引的tabTac"IX_tabTac"。

唯一索引IX_tabTac:

CREATE UNIQUE NONCLUSTERED INDEX [IX_tabTac] ON [dbo].[tabTAC] 
(
    [TAC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

Table tabTac:

CREATE TABLE [dbo].[tabTAC](
    [idTAC] [int] IDENTITY(1,1) NOT NULL,
    [TAC] [char](8) NOT NULL,
    [fiModel] [int] NOT NULL,
 CONSTRAINT [PK_tabTac] PRIMARY KEY CLUSTERED 
(
    [idTAC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tabTAC]  WITH NOCHECK ADD  CONSTRAINT [FK_tabTac_modModel] FOREIGN KEY([fiModel])
REFERENCES [dbo].[modModel] ([idModel])
GO
ALTER TABLE [dbo].[tabTAC] CHECK CONSTRAINT [FK_tabTac_modModel]

store - procedure InsertTacsFromClaims:

CREATE PROC [dbo].[InsertTacsFromClaims]
with execute as Owner
AS
BEGIN
    INSERT INTO tabTac
        select substring(t.SSN_Number,1,8)as TAC
        ,m.idModel as fiModel
        from tabData t 
        inner join modmodel m 
            ON t.fimodel=m.idmodel 
        WHERE  t.fiproducttype=1
        and m.idModel>1
        and not exists(
            select fiModel from tabTac WHERE TAC=substring(t.SSN_Number,1,8)
        )
        GROUP BY substring(t.SSN_Number,1,8),m.idModel
END 
RETURN @@ROWCOUNT;

我本以为我可以用:

来避免这个错误

and not exists(
            select fiModel from tabTac WHERE TAC=substring(t.SSN_Number,1,8)
          )

编辑:

@Sparkys的第二个查询(ModelName添加)返回如下:

TAC         fiModel    ModelName
01233300    777        U5A
01238300    771        W20I
01238300    784        W20
35427603    720        C903
35773704    781        E15I
35905104    451        W595
35946804    793        W150I
35959004    813        ST18I

现在很明显，tabData中的一个TAC (SSN_Number的前8个字符)没有一个独特的模型，但可以链接到多个模型。这会导致错误，因为在tabTac中TAC必须是唯一的。

Thanks in advance

Answer 1

检查SSN_Number是否在TabData表中重复，或者连接到modModel是否产生重复。

select substring(t.SSN_Number,8),count(*)
from tabData
groub by substring(t.SSN_Number,8)
having count(*) > 1

和

  select substring(t.SSN_Number,1,8)as TAC
        ,m.idModel as fiModel
        from tabData t 
        inner join modmodel m 
            ON t.fimodel=m.idmodel 
        WHERE  t.fiproducttype=1
        and m.idModel>1
        GROUP BY substring(t.SSN_Number,1,8),m.idModel
        HAVING count(*) > 1

如果任何一个查询返回重复的ssn_number，您将得到错误。您的代码只检查如果SSN号已经存在于您试图填充的表中，则不会从表中添加SSN号，但不会考虑已经存在的数据中的潜在副本

有几种方法可以只获取最新的fiModel代码，下面是一个例子:

select SSN_Number,fiModel 
FROM tabData td1
JOIN
(  -- This will get the latest date from tabData
select SSN_Number,max(received_date) as TheLastestDate
FROM tabData td
LEFT JOIN tabTac tc on tc.TAC = substring(TD.SSN_number,1,8) 
                       and tc.fiModel = td.fModel
WHERE tc.fiModel is NULL
GROUP BY SSN_Number) xx ON xx.SSN_number=td1.ssn_number 
                           and td1.received_date = xx.TheLatestDate

请注意，如果您在同一日期有多个fimodel，上面的代码将无法处理它，因为它不知道如何区分使用哪个。