日期唯一约束

Question

日期唯一约束

3

我需要在Oracle数据库表中添加一个唯一约束条件，其中只有当两个日期列不重叠时，外键引用才能存在多次。

例如：

car_id  start_date  end_date
3       01/10/2012  30/09/2013  
3       01/10/2013  30/09/2014  -- okay no overlap
3       01/10/2014  30/09/2015  -- okay no overlap
4       01/10/2012  30/09/2013  -- okay different foregin key
3       01/11/2013  01/01/2014  -- * not allowed overlapping dates for this car.

有什么建议吗？提前感谢。

- solarwind

触发器对于这种情况会更好。 - Chaitanya Kotha

2

对我来说，最佳实践是不使用触发器（只有在确实不需要时才避免使用它们是一种常见做法）。我会使用一个过程来设置/获取表中的行，以检查是否存在重叠。然后，如果存在重叠，只需返回错误即可。 - przemo_pl

1

@chaitanyakvv 不应该滥用触发器。 - Lalit Kumar B

如果 Oracle 有范围类型和排除约束就好了... - user330315

添加检查约束。这将有助于实现条件唯一性约束。 - Muhammad Muazzam

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Florin Ghita · Answer 1

上次我看到一个关于这个问题的需求和解决方案，是这样的：

创建一个后语句触发器。在这个触发器中，像这样对您的表进行自连接：

select count(*)
from your_table a 
join your_table b
on a.car_id = b.car_id and
  (a.start_date between b.start_date and b.end_date
   or 
   b.start_date between a.start_date and a.end_date)

如果计数为零，则一切正常。如果计数> 0，则引发异常，语句将被回滚。

OBS：这对于具有>数百万行和许多插入的表不起作用。它适用于小型查找表，或者如果您有一个大表，则使用大表和很少的插入（批量插入）。

- TommCatt · Answer 2

我理解汽车是通过某种过程进行跟踪的，每个日期记录一个状态变化。例如，您展示了汽车#3在2012年10月1日、2013年10月1日和2014年10月1日经历了状态变化。最后一项条目意味着状态在2015年10月1日再次发生了变化。那么显示这一点的条目在哪里？或者状态是否总是持续一年——使得可以在状态开始时指定状态的结束？如果是这样，那么显示2013年11月1日状态变化的条目就是错误的。但是一年的规定可能只是巧合。您可能只是选择了简单的数据点作为示例数据。

您现在关心的是严格区分有效数据和准确数据。我们设计数据库（或应该）强调数据完整性或有效性。这意味着我们尽可能地限制每个数据片段，使其与该数据片段的规格说明一致。

例如，汽车ID字段是外键——通常是指定义每个汽车实体的表。因此，我们知道至少存在两辆汽车，其ID为3和4。否则，这些值不能存在于您展示的示例中。

但是准确性或正确性呢？假设在您的示例中的最后一个条目中，汽车ID 3实际上应该是4？从数据库内部无法判断。这说明了区别。3和4都是有效值，我们能够将其限制为仅有效值。但只有一个是正确的——暂时假设它们是唯一定义的两辆车。关键是，没有测试，也没有办法将值约束为正确的值。我们可以检查有效性——而不是准确性。

您试图做的是通过有效性测试来检查准确性。您可能声称“无重叠”限制成为有效性检查，但这只是一种准确性检查。有时我们可以执行测试以发出数据异常信号，表明某处存在不准确性。例如，重叠可能意味着2014年9月30日的结束日期（第二行）是错误的，或者2013年11月1日的开始日期（最后一行）是错误的，或者两者都可能是错误的。我们不知道这代表哪种情况。因此，我们不能仅仅防止将最后一行输入到数据库中——它可能是正确的，而第二行是不正确的。

无效数据本身就是无效的。假设尝试插入汽车ID为15的行，但CARS表中没有15号汽车的条目。那么值15就是无效的，应该防止该行进入表格（并且应该）。但日期重叠是由错误数据“某处”引起的 - 我们无法确切知道在哪里。我们可以向用户发出不一致信号或在某个地方进行日志记录以便有人查看问题，但当可能是包含错误数据的现有行导致重叠时，我们不应拒绝“导致”重叠的行。

准确性，就像数据本身一样，源自数据库外部。如果我们足够幸运能够检测到不准确的实例，解决方案也在数据库外部。我们所能做的最好的事情就是标记它，并让某人调查以确定哪些数据是正确的，哪些是不正确的，并（希望）纠正不准确性。

更新：在讨论了数据完整性和准确性的概念以及它们之间的区别后，这里提供一个可能是改进的设计思路。

注意：这基于日期范围对于每辆汽车从第一次输入到最后一次形成一个不间断的范围的假设。也就是说，没有间隙。

简单来说，完全取消end_date字段。车辆的第一条记录设置了该车辆的当前状态，没有指定结束日期。明显的暗示是该状态将无限期地持续到未来的下一个状态更改被插入为止。第二个状态更改的开始日期成为第一个状态更改的结束日期。如有需要，请继续进行。

create table Car_States(
    Car_ID     int not null,
    Start_Date date not null,
    ...,      -- other info
    constraint FK_Car_States_Car foreign key( Car_ID )
        references Cars( ID ),
    constraint PK_Car_States primary key( Car_ID, Start_Date )
);

现在让我们来看一下数据

car_id  start_date
3       01/10/2012
3       01/10/2013  -- okay no overlap
3       01/10/2014  -- okay no overlap
4       01/10/2012  -- okay different foreign key
3       01/11/2013  -- What does this mean???

在输入最后一行之前，以下是读取id = 3的汽车数据的方式：汽车3于2012年10月1日以特定状态开始生活，在2013年10月1日更改为另一种状态，然后在2014年10月1日再次更改状态并保持不变。

现在输入了最后一行：汽车3于2012年10月1日以特定状态开始生活，在2013年10月1日更改为另一种状态，在2013年11月1日再次更改状态，然后在2014年10月1日再次更改状态并保持不变。

正如我们所看到的，我们能够轻松地将新数据吸收到模型中。该设计使得不可能存在间隙或重叠。

但这真的是一种改进吗？如果最后一条记录是错误的——可能是针对3号车而不是其他车辆？或者输入了错误的日期。新模型只是接受了不正确的数据而没有投诉，我们继续使用表格而不知道其中存在不正确的数据。

这是真的。但它与原始情况有何不同呢？最后一行代表“错误”的数据。问题是，“我该如何防止这种情况？”答案是，在这两种情况下，“你不能！抱歉。”任何设计都能做到的最好的事情就是检测到差异并引起某人的注意。

有人可能认为，在原始设计中，开始和结束日期在同一行中，很容易确定新期间是否重叠了以前定义的期间。但是，使用仅开始日期的设计也可以轻松确定这一点。重要的是，在将数据写入表之前，发现此类可能的不准确性的测试主要取决于应用程序，而不仅仅是数据库内部。

验证新数据和现有数据是否存在任何不准确性是由用户和/或某些自动化过程来完成的。仅使用一个日期的优点在于，在显示带有“您确定吗？”响应的警告消息后，可以插入新记录并完成操作。对于两个日期，必须找到其他记录并将它们的日期重新同步以匹配新期间。