如何确保理货台的连续性？

Question

在SQL ForSmarties中，JoeCelko提供了Series表（在其他地方称为Tally或Numbers）的ANSI SQL定义。他的定义确保列中的值从1到最大值都是唯一的，正的和连续的：

CREATE TABLE Series (
  seq INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT non_negative_nbr CHECK (seq > 0),
  CONSTRAINT numbers_are_complete CHECK ((SELECT COUNT(*) FROM Series) = (SELECT MAX(seq) FROM Series))
);

通过PRIMARY
KEY声明确保唯一性。约束确保了积极性non_negative_nbr。有了这两个约束，约束就可以确保连续性numbers_are_complete。

SQL Server在检查约束中不支持子查询。当我尝试创建系列表时，出现如下错误：

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

如果删除不支持的约束numbers_are_complete，则剩下以下定义：

CREATE TABLE Series (
  seq INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT non_negative_nbr CHECK (seq > 0)
);

当我尝试创建此版本的Series时，它会成功：

Command(s) completed successfully.

此版本的Series较弱，因为它不强制表中数字的连续性。

为了证明这一点，首先我必须填充表格。我采用了Itzik Ben-
Gan在他的文章“虚拟数字辅助表”中描述的技术，以有效地对65,536行执行此操作：

WITH
N0(_) AS (SELECT NULL UNION ALL SELECT NULL),
N1(_) AS (SELECT NULL FROM N0 AS L CROSS JOIN N0 AS R),
N2(_) AS (SELECT NULL FROM N1 AS L CROSS JOIN N1 AS R),
N3(_) AS (SELECT NULL FROM N2 AS L CROSS JOIN N2 AS R),
N4(_) AS (SELECT NULL FROM N3 AS L CROSS JOIN N3 AS R)
INSERT INTO Series (
  seq
)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM N4;

查询产生如下输出：

(65536 row(s) affected)

现在，我可以从这样的表中进行选择，以产生65,536行：

SELECT seq
FROM Series;

我已经截断了结果集，但它看起来像这样：

seq
1
2
...
65535
65536

自己检查一下，您会发现结果集中有[1，65536]区间中的每个数字。该系列是连续的。

但是我可以通过删除不是该范围端点的任何行来破坏连续性：

DELETE FROM Series
WHERE seq = 25788;

如果强制执行连续性，则此语句将引发错误，但它将成功：

(1 row(s) affected)

对于人类来说，通过目视检查来发现缺失值将是困难的。他们必须先怀疑某个值是否丢失，然后再去解决问题。出于这些原因，篡改Series数据是将依赖于Series表连续的SQL
Server应用程序中引入细微错误的简便方法。

假设用户编写了一个查询，该查询从Sequence读取以枚举来自另一个源的行。经过我的篡改之后，该查询现在将在某个值附近产生错误的结果-
到第25788行，一切都减少了一个。

可以编写查询来检测“序列”表中的缺失值，但是如何约束该表以使缺失值不可行？

Answer 1

我有三个潜在的建议：

（1）将您的数字表设为只读（例如，拒绝更新/插入/删除）。EVER，您为什么要从此表中删除？您的应用程序当然不应该这样做，您的用户也不应该手动进行操作。用户按下“此按钮的作用是什么？”时，不需要所有这些检查约束。按钮，则只需删除按钮即可。

DENY DELETE ON dbo.Serial TO [your_app_user];
-- repeat for individual users/roles

（2）更简单的方法是创建一个触发器而不是触发器来防止删除操作：

CREATE TRIGGER dbo.LeaveMyNumbersAlone
ON dbo.Serial
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;
  RAISERROR('Please leave my numbers table alone.', 11, 1);
END

是的，这是可以克服的，但必须有人真正做到这一点。而且，如果您雇用的人可能会这样做，并通过对数据库的通用访问权来信任他们，请祈祷这是他们计划造成的最大损失。

是的，如果您删除/重新创建数字表或在其他地方实现它，您可能会忘记重新实现触发器。但是您也可能会忘记手动解决差距的任何操作。

（3）如果您愿意随时获取数字，则可以完全避免使用数字表。我为此使用sys.all_columns和sys.all_objects之类的目录视图，具体取决于我需要多少个数字：

;WITH n AS (SELECT TOP (10000) n FROM 
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

如果只需要100行，则可以只使用其中一个视图而无需交叉联接。如果需要更多，可以添加更多视图。不试图将您从数字表中移开，但这可以解决一些局限性，例如（a）在每个实例上构建数字表，以及（b）在哲学上反对这种事情的人（我在我的书中遇到过很多职业）。