sql - 表列的顺序约束

Question

假设我有一个表，其中包含 created、uploaded、processed 和 archived 列，所有这些接受约会。但是，我想要它，所以字段必须按时间顺序更新，你不能跳过一个步骤，但是在准备好之前不必设置尾随步骤(即创建 <= 上传 <= 处理<= 存档)。我是这样实现的:

create table batch (
  id        number primary key,
  created   date   default sysdate not null,
  uploaded  date,
  processed date,
  archived  date,

  check (uploaded  is null or uploaded  >= created),
  check (processed is null or processed >= uploaded),
  check (archived  is null or archived  >= processed)
);

这可行，但它允许您跳过步骤。例如，如果 uploaded 仍然是 null，我可以为 processed 插入一个任意值。我认为这是因为 null 比较总是空洞的。因此，我是否必须执行以下操作:

create table batch (
  id        number primary key,
  created   date   default sysdate not null,
  uploaded  date,
  processed date,
  archived  date,

  check (uploaded  is null or uploaded >= created),
  check (processed is null or (uploaded is not null and processed >= uploaded)),
  check (archived  is null or (processed is not null and archived >= processed))
);

有没有更简洁的方法？

Answer 1

我认为您的设计很合理。我会将时间顺序检查和 NULL 检查分开，但这只是风格问题，而不是逻辑问题。

create table batch (
  id        number primary key,
  created   date   default sysdate not null,
  uploaded  date,
  processed date,
  archived  date,

  CHECK (created <= uploaded),
  CHECK (uploaded <= processed),
  CHECK (processed <= archived),

  CHECK (uploaded IS NOT NULL OR processed IS NULL),
  CHECK (processed IS NOT NULL OR archived IS NULL)
);