SQL约束，以防止基于列的先前值更新列 [英] SQL constraint to prevent updating a column based on its prior value

问题描述

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

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

这篇关于SQL约束，以防止基于列的先前值更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！