SQL约束，以防止基于列的先前值更新列

本文介绍了SQL约束，以防止基于列的先前值更新列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

是否可以使用检查约束（或其他某种技术）来防止在记录更新时设置与先前值相矛盾的值。

Can a Check Constraint (or some other technique) be used to prevent a value from being set that contradicts its prior value when its record is updated.

一个示例将是NULL时间戳，指示发生了某些事情，例如 file_exported。一旦文件导出并且具有非NULL值，就永远不要再次将其设置为NULL。

One example would be a NULL timestamp indicating something happened, like "file_exported". Once a file has been exported and has a non-NULL value, it should never be set to NULL again.

另一个例子是计数器，其中一个整数是仅允许增加，但不能减少。

Another example would be a hit counter, where an integer is only permitted to increase, but can never decrease.

如果有帮助，我正在使用Postgresql，但我希望看到适合任何SQL实现的解决方案

If it helps I'm using postgresql, but I'd like to see solutions that fit any SQL implementation

推荐答案

一个示例是NULL时间戳，指示发生了某些事情，例如，例如 file_exported。一旦文件导出并且具有非NULL的值，就永远不要再次将其设置为NULL。

One example would be a NULL timestamp indicating something happened, like "file_exported". Once a file has been exported and has a non-NULL value, it should never be set to NULL again.

另一个例子是计数器，在其中仅允许增大整数，但永远不允许减小的整数。

Another example would be a hit counter, where an integer is only permitted to increase, but can never decrease.

在这两种情况下，我都不会请勿将这些更改记录为带注释的表中的属性； 导出或命中计数是一个独特的想法，代表与它们相关的对象的相关但正交的现实世界概念：

In both of these cases, I simply wouldn't record these changes as attributes on the annotated table; the 'exported' or 'hit count' is a distinct idea, representing related but orthogonal real world notions from the objects they relate to:

因此，它们只是不同的关系。由于我们只希望 file_exported发生一次：

So they would simply be different relations. Since We only want "file_exported" to occur once:

CREATE TABLE thing_file_exported( thing_id INTEGER PRIMARY KEY REFERENCES(thing.id), file_name VARCHAR NOT NULL )

命中计数器为类似地，另一个表：

The hit counter is similarly a different table:

CREATE TABLE thing_hits( thing_id INTEGER NOT NULL REFERENCES(thing.id), hit_date TIMESTAMP NOT NULL, PRIMARY KEY (thing_id, hit_date) )

然后您可以使用

SELECT thing.col1, thing.col2, tfe.file_name, count(th.thing_id) FROM thing LEFT OUTER JOIN thing_file_exported tfe ON (thing.id = tfe.thing_id) LEFT OUTER JOIN thing_hits th ON (thing.id = th.thing_id) GROUP BY thing.col1, thing.col2, tfe.file_name