英文:
Does SQLite check constraint allow checking against existing data?
问题
SQLite的CHECK约束是否支持对现有数据进行检查？我刚刚阅读了这篇文章，https://www.sqlitetutorial.net/sqlite-check-constraint/，我的印象是检查只能应用于正在插入的数据，而不包括现有数据。
这是我的使用案例，假设数据库已经有几行整数数据，并且它们都遵循这个规则，没有数据小于最小数据的一半。现在我想对要插入的所有数据强制执行这个规则。
例如，假设我已经有以下数据，10、11、12、15、16。可以添加6，因为它大于5，而5是最小数据的一半，最小数据是10；但不能添加3，因为它小于5。
谢谢！
英文:
Does SQLite check constraint supports checking against existing data? I just read this article, https://www.sqlitetutorial.net/sqlite-check-constraint/, my impression is the check can only apply on the data being inserted, not including existing data.
Here is my use case, suppose the database already have several rows of integer data and they all follow this rule, no data is less than half of the smallest data. Now I like to enforce this rule on all the data to be inserted.
For example, say, I have the following data already, 10, 11, 12, 15, 16. It is ok to add 6 since it is larger than 5 which is half of the smallest data which is 10; but it should not allow adding 3 because it is less than 5.
Thanks!
答案1
得分: 0
自创建表/检查约束：
> CHECK约束的表达式不能包含子查询。
由于获取列的最小值的唯一方法是查询，这意味着无法使用CHECK约束来执行此操作。<br/>
使用触发器：
CREATE TRIGGER trg_value BEFORE INSERT ON tablename
BEGIN
SELECT
CASE
WHEN NEW.value < (SELECT MIN(value) FROM tablename) / 2
THEN RAISE (ABORT, '无效值')
END;
END;
请参阅demo。<br/>
英文:
From CREATE TABLE/CHECK constraints:
> The expression of a CHECK constraint may not contain a subquery.
Since the only way to get the min value of the column is a query this means that it is not possible to do it with a CHECK constraint.<br/>
Use a trigger:
CREATE TRIGGER trg_value BEFORE INSERT ON tablename
BEGIN
SELECT
CASE
WHEN NEW.value < (SELECT MIN(value) FROM tablename) / 2
THEN RAISE (ABORT, 'Invalid value')
END;
END;
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论