在两列上创建约束以支持特定规则

Question

>我有一个存储设备详细信息的表。为简单起见，这些列是：

Id (Primary Key)
Name (varchar)
StatusId (Foreign Key to Status table).

"状态"表包含两列：

Id (Primary Key)
State (varchar)

和两行：

[Id | State]
  1 | Active
  2 | Inactive

我想允许"设备"表中的多个设备具有相同的名称，但其中只有一个设备可以随时具有活动状态。

也就是说，这应该在"设备"表中允许：

[Id | Name      | StatusId]
 10 | Mobile001 | 1
 11 | Mobile001 | 2
 12 | Mobile001 | 2
 20 | Tablet001 | 1
 21 | Tablet002 | 2
 22 | Tablet002 | 1
 23 | Tablet003 | 2

但这不应该被允许：

[Id | Name      | StatusId]
 10 | Mobile001 | 1    <-- wrong
 11 | Mobile001 | 1    <-- wrong
 12 | Mobile001 | 2    
 20 | Tablet001 | 1
 21 | Tablet002 | 1    <-- wrong
 22 | Tablet002 | 1    <-- wrong
 23 | Tablet003 | 2    

有没有办法在 T-SQL 中创建约束以拒绝违反此规则的插入和更新？有没有办法首先使用 EntityTypeConfigurations 和 Fluent API 在 EF 代码中做到这一点，可能通过 IndexAnnotation 或 IndexAttributes？

谢谢。

Answer 1

正如刚才评论@ZoharPeled的，一种方法是使用过滤后的唯一索引。

由于只允许一个特定名称的活动设备，因此可以按如下方式实现：

USE Sandbox;
GO
--Create sample table
CREATE TABLE Device (ID int IDENTITY(1,1),
                     [name] varchar(10),
                     [StatusID] int);
--Unique Filtered Index
CREATE UNIQUE INDEX ActiveDevice ON Device ([name], [StatusID]) WHERE StatusID = 1;
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 1); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 0); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile2', 1); --Works
GO
INSERT INTO Device ([name], StatusID)
VALUES ('Mobile1', 1); --Fails
GO
UPDATE Device
SET StatusID = 1
WHERE ID = 2; --Also fails
GO
SELECT *
FROM Device;
GO
DROP TABLE Device;

如有任何问题，请提问。

Answer 2

在 EF CF 中，您可以通过设置唯一索引来实现它，如本答案中所述。

modelBuilder.Entity<Device>()
    .HasIndex(d => new { d.Name, d.StatusId })
    .IsUnique();