SQL Server 2008数据库中字段的复杂约束

Question

我有以下（简化的）结构，允许我跟踪已分配给单元的设备。由于设备只能在表中的单元格中出现一次，因此我创建了一个约束，规定 idEquipment 和 idCell 在表中必须是唯一的。

CREATE TABLE [dbo].[CellEquipment](
    [idEquipment] [int] NOT NULL,
    [idCell] [int] NULL,
 CONSTRAINT [PK_CellEquipment] UNIQUE NONCLUSTERED 
(
    [idEquipment] ASC,
    [idCell] ASC
)

这个限制确保我永远不会将同一件设备添加到一个单元中两次。

所以现在我的任务是保存历史信息。我需要能够提取工作订单，查看其日期，然后找到该工作订单使用的设备。解决方案是将日期信息添加到表中，如下所示：

CREATE TABLE [dbo].[CellEquipment](
    [idEquipment] [int] NOT NULL,
    [idCell] [int] NULL,
    [DateAdded] [datetime] NULL,
    [DateRemoved] [datetime] NULL,
)

现在我上面的约束被打破了。 idCell/idEquipment 不再是唯一的，因为设备可以移除并重新添加到单元中。现在我有一些棘手的约会问题需要解决。为了确保数据完整性，对于数据库的更改必须满足以下条件：

idCell/idEquipment are unique (like before)   OR
idCell/idEquipment's new DateAdded doesn't fall between a DateAdded/Removed OR
idCell/idEquipment's new DateRemoved doesn't fall between a DateAdd/Removed or
idCell/idEquipment's doesn't have a record with DateRemoved=NULL

检查约束没有能力实现这一点，唯一索引约束也无法做到这一点。顺便说一句，可以创建检查约束来确保 DateAdded DateAdded DateAdded DateAdded DateAdded DateAdded DateAdded DateRemoved（以及其他 NULL/NOT NULL 约束关系）

我是否需要从代码、事务、不同的模型中强制执行这些关系？

也许有一种我不知道的设计模式可以帮助存储此类历史数据？

Answer 1

因为我不知道设备和细胞对你来说意味着什么，所以我在这里可能会大错特错。

但在我看来，重要的信息在于“工单编号中使用了哪些设备？”这个问题。日期并不能真正给你这些信息。但这个（空中代码）结构可能会。

create table work_order_equpiment (
  idEquipment integer not null,
  idCell integer not null,
  foreign key (idEquipment, idCell) references CellEquipment (idEquipment, idCell),
  work_order_number integer not null references workorders (work_order_number),
  primary key (idEquipment, idCell, work_order_number)
);

这使得在给定的工作订单上使用设备变得非常简单。要获取给定日期使用的设备，请连接 CellEquipment、work_order_equipment 和工作订单，然后查看工作订单表中的日期。