过滤唯一约束或类似工具

Question

我需要想出一个解决方法，来处理一个设计错误，但现在已经为时过晚。基本上我预计会有重复的数据进入到一个被特定结构避免这种情况的表中:

CREATE TABLE building (
    building_id INT IDENTITY(1, 1) NOT NULL,
    address_id INT NOT NULL,
    company_id INT NOT NULL,
    CONSTRAINT building_pk PRIMARY KEY (building_id),
    CONSTRAINT building_fk1 FOREIGN KEY (address_id) REFERENCES address (address_id),
    CONSTRAINT building_fk2 FOREIGN KEY (company_id) REFERENCES company (company_id)
);
CREATE INDEX building_idx1 ON building (address_id);
CREATE INDEX building_idx2 ON building (company_id);

/* Prevent dupes */
ALTER TABLE building ADD CONSTRAINT building_uk1 UNIQUE (address_id);

^{(为更清晰和相关于问题，原始本地化名称已更改。)}

Acme公司的"导出建筑物"API将发送共享相同地址的不同建筑物。删除building_uk1将破坏某些假定唯一性的功能，而我们在此阶段无法承担重大改写。

我想评估禁用building_uk1对上述公司（company_id=314）的影响，该公司可以不使用依赖于唯一性的功能。那么，放宽building_uk1并仅在company_id不是314时强制执行的选项是什么？

- Álvaro González

Answer 1

一个简单且独特的筛选索引就足够了：

CREATE UNIQUE NONCLUSTERED INDEX [IX_building_uk1] ON [dbo].[building]
(
    [address_id] ASC
)
WHERE (company_id <> 314)

- Vladimir Baranov

---

文档对支持的版本并不是很清楚，但在 SQL Server 2008 中似乎可以正常工作。 - Álvaro González

@ÁlvaroGonzález，它在2008年肯定有效。我在我们的系统中的几个地方都在使用它。看起来微软在发布2016年后从MSDN中删除了2008年的文档。 - Vladimir Baranov

Answer 2

您可以通过检查约束来实现这一点，让我看看我是否正确理解了您的要求

创建一个函数，如果不是building_id 314，则检查distinct

CREATE FUNCTION CheckDistinctConstraint ()
RETURNS int
AS
BEGIN
  DECLARE @retValue int = 0
  DECLARE @cnt int = 0
  DECLARE @distcnt int = 0
  SELECT
    @cnt = COUNT(*),
    @distcnt = COUNT(DISTINCT address_id)
  FROM building
  WHERE company_id <> 314
  IF @cnt <> @distcnt
  BEGIN
    SET @retValue = 1
  END
  RETURN @retValue
END

通过应用此函数添加约束

ALTER TABLE dbo.Building
  ADD CONSTRAINT CheckDistConstraint CHECK (dbo.CheckDistinctConstraint() =0);

创建表脚本

CREATE TABLE building (
    building_id INT  NOT NULL,
    address_id INT NOT NULL,
    company_id INT NOT NULL--,
    CONSTRAINT building_pk PRIMARY KEY (building_id)--,
    --CONSTRAINT building_fk1 FOREIGN KEY (address_id) REFERENCES address (address_id),
    --CONSTRAINT building_fk2 FOREIGN KEY (company_id) REFERENCES company (company_id)
);

--inserts successfully
insert into building ( building_id, address_id, company_id) values
(1,11,22)

--insert failed bcos check constraint
insert into building ( building_id, address_id, company_id) values
(5,11,315)

-- insert successfull eventhough duplicate in address_id but building_id is 314
insert into building ( building_id, address_id, company_id) values
(311,11,314)

不确定你是否在寻找类似这样的东西？

- Kannan Kandasamy

---

是的，它像其他检查约束一样工作，关于性能，我们可以找到调整函数的方法。 - Kannan Kandasamy

更新了我的脚本以检查公司ID。 - Kannan Kandasamy

小心使用调用UDF的约束条件。 - Vladimir Baranov

@VladimirBaranov 很不错的信息。虽然我认为在我的情况下这并不重要（没有GUI或代码会更新company_id，而在数据库中手动更新会破坏许多其他东西），但它并不像应该那样健壮。 - Álvaro González

@ÁlvaroGonzález，如果过滤索引足以满足您的需求，请使用它。它始终是正确的，并且其性能很可能比UDF更好。它还可以帮助处理具有与索引相同的“WHERE”条件的某些查询。仅当约束的逻辑过于复杂时，才考虑其他方法，例如触发器和约束中的UDF。 - Vladimir Baranov