我可以添加一个忽略现有违规的唯一约束吗？

#1 楼

答案是“是”。您可以使用过滤索引来执行此操作（请参见此处以获取文档）。例如，您可以执行以下操作：

create unique index t_col on t(col) where id > 1000;

这将创建一个唯一索引，仅在新行上，而不在旧行上。这种特定的表述将允许具有现有值的重复项。

如果只有少量重复项，则可以执行以下操作：

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);

#2 楼

是的，你可以这样做。

这是一个包含重复项的表：

CREATE TABLE dbo.Party
  (
    ID INT NOT NULL
           IDENTITY ,
    CONSTRAINT PK_Party PRIMARY KEY ( ID ) ,
    Name VARCHAR(30) NOT NULL
  ) ;
GO

INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' ),
        ( 'Luke Skywalker' ),
        ( 'Luke Skywalker' ),
        ( 'Harry Potter' ) ;
GO

让我们忽略现有的重复项，并确保不能添加任何新的重复项：

-- Add a new column to mark grandfathered duplicates.
ALTER TABLE dbo.Party ADD IgnoreThisDuplicate INT NULL ;
GO

-- The *first* instance will be left NULL.
-- *Secondary* instances will be set to their ID (a unique value).
UPDATE  dbo.Party
SET     IgnoreThisDuplicate = ID
FROM    dbo.Party AS my
WHERE   EXISTS ( SELECT *
                 FROM   dbo.Party AS other
                 WHERE  other.Name = my.Name
                        AND other.ID < my.ID ) ;
GO

-- This constraint is not strictly necessary.
-- It prevents granting further exemptions beyond the ones we made above.
ALTER TABLE dbo.Party WITH NOCHECK
ADD CONSTRAINT CHK_Party_NoNewExemptions 
CHECK(IgnoreThisDuplicate IS NULL);
GO

SELECT * FROM dbo.Party;
GO

-- **THIS** is our pseudo-unique constraint.
-- It works because the grandfathered duplicates have a unique value (== their ID).
-- Non-grandfathered records just have NULL, which is not unique.
CREATE UNIQUE INDEX UNQ_Party_UniqueNewNames ON dbo.Party(Name, IgnoreThisDuplicate);
GO

让我们测试此解决方案：

-- cannot add a name that exists
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- cannot add a name that exists and has an ignored duplicate
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Luke Skywalker' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.


-- can add a new name 
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

-- but only once
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

#3 楼

过滤后的唯一索引是一个绝妙的主意，但是它有一个次要的缺点-无论使用WHERE identity_column > <current value>条件还是WHERE identity_column NOT IN (<list of ids for duplicate values here>)。

使用第一种方法，您仍然可以在其中插入重复数据将来，复制现有（现在）数据。例如，如果您现在（甚至只有一行）的行带有CompanyName = 'Software Inc.'，那么索引将不会再插入另一行具有相同公司名称的行。

第二种方法有了改进，上面的方法不起作用（这很好）。但是，您仍然可以插入更多重复项或现有重复项。例如，如果现在具有CompanyName = 'DoubleData Co.'（具有两行或更多行），则索引不会禁止再插入具有相同公司名称的另一行。仅当您尝试两次时才会禁止它。

（更新）如果对于每个重复的名称，您都在排除列表中保留一个ID，则可以更正此错误。如果像上面的示例一样，如果有4行具有重复的CompanyName = DoubleData Co.和ID为4,6,8,9，则排除列表应仅包含这些ID中的3个。

采用第二种方法的另一个缺点是麻烦的条件（多少繁琐的操作首先取决于有多少个重复项），因为SQL-Server在过滤索引的NOT IN部分中似乎不支持WHERE运算符。请参见SQL-Fiddle。除了WHERE (CompanyID NOT IN (3,7,4,6,8,9))之外，您还必须具有类似WHERE (CompanyID <> 3 AND CompanyID <> 7 AND CompanyID <> 4 AND CompanyID <> 6 AND CompanyID <> 8 AND CompanyID <> 9)的内容。如果您有数百个重复的名称，我不确定这种情况是否会影响效率。


另一种解决方案（类似于@Alex Kuznetsov的方法）是添加另一列，用等级号填充它，并添加包括该列的唯一索引：

ALTER TABLE Company
  ADD Rn TINYINT DEFAULT 1;

UPDATE x
SET Rn = Rnk
FROM
  ( SELECT 
      CompanyID,
      Rn,
      Rnk = ROW_NUMBER() OVER (PARTITION BY CompanyName 
                               ORDER BY CompanyID)
    FROM Company 
  ) x ;

CREATE UNIQUE INDEX CompanyName_UQ 
  ON Company (CompanyName, Rn) ; 

然后，由于DEFAULT 1属性和唯一索引，将无法插入具有重复名称的行。这仍然不是100％万无一失的（而Alex是）。如果在Rn语句中显式设置了INSERT，或者恶意更新了Rn值，重复项仍会遗漏。

SQL-Fiddle-2

#4 楼

另一种选择是编写一个标量函数，该函数检查表中是否已经存在一个值，然后从检查约束中调用该函数。

这将对性能造成可怕的影响。

#5 楼

我正在寻找相同的对象-创建一个不受信任的唯一索引，以便忽略现有的不良数据，但是新记录不能与已存在的任何内容重复。

在阅读此线程时，它涉及到我认为更好的解决方案是编写一个触发器，该触发器将针对父表检查[插入]是否存在重复，如果在这些表之间存在任何重复，则使用ROLLBACK TRAN。