SQLite：确保一个条目小于另一条目作为约束

Question

我想知道如何解决这个问题。

我有两个表

Table1 (t1_prim, t1_int) 以 t1_prim 作为主键，t1_int 不为空。

表2（t2_prim1、t2prim2、t2_int） 以 t2_prim1 和 t2_prim2 作为主键和外键约束 t2_prim1 引用 t1_prim。

如何定义一个约束来确保没有人在 t2_int 中输入大于 t1_int 中相应条目的整数？

我这样尝试（不起作用，因为你无法在检查约束中输入子查询）：

CREATE TABLE table2 
  (t2_prim1 TEXT,
  t2_prim2 INTEGER,
  t2_int INTEGER NOT NULL,
  PRIMARY KEY (t2_prim1, t2_prim2),
  FOREIGN KEY (t2_prim1) REFERENCES table1(t1_prim),
  CHECK (t2_int2 <= (SELECT t1_int2 FROM table1 WHERE t1_int1=t2_int1)));

而且我认为如果它像这样工作，还会有另一个问题。在更改 t1_int 时，如何检查该约束是否仍然满足？

Answer 1

终于找到了我的问题的答案。您可以通过使用触发器来解决问题。这是一个工作示例：

CREATE TABLE table1
  (t1_prim TEXT PRIMARY KEY,
   t1_int INTEGER NOT NULL);

CREATE TABLE table2
  (t2_prim1 TEXT,
  t2_prim2 INTEGER,
  t2_int INTEGER NOT NULL,
  PRIMARY KEY (t2_prim1, t2_prim2),
  FOREIGN KEY (t2_prim1) REFERENCES table1(t1_prim));

创建用于更新和插入的触发器：

CREATE TRIGGER t1_int_update_constraint
BEFORE UPDATE OF t1_int ON table1
  BEGIN
    SELECT CASE 
      WHEN new.t1_int < (SELECT max(t2_int) FROM table2 WHERE t2_prim1=old.t1_prim) 
        THEN (SELECT RAISE(ABORT, 
          'Input smaller than maximum of all values for t2_int in database!'))
      END;
  END;

CREATE TRIGGER t2_int_update_constraint
BEFORE UPDATE OF t2_int ON table2
  BEGIN
    SELECT CASE 
      WHEN new.t2_int > (SELECT t1_int FROM table1 WHERE t1_prim=old.t2_prim1) 
        THEN (SELECT RAISE(ABORT, 
         'Input bigger than value in t1_int!'))
      END;
    END;

CREATE TRIGGER t2_int_insert_constraint
BEFORE INSERT ON table2
  BEGIN
    SELECT CASE 
      WHEN new.t2_int > (SELECT t1_int FROM table1 WHERE t1_prim=new.t2_prim1) 
        THEN (SELECT RAISE(ABORT, 
         'Input bigger than value in t1_int!'))
      END;
  END;

如果您现在有以下表：

table1

t1_prim     t1_int
----------  ----------
one         5         
two         10   

table2

t2_prim1    t2_prim2    t2_int    
----------  ----------  ----------
one         1           5         
one         2           4         
two         1           7         
two         2           5         
two         3           1         

您将得到以下输出：

UPDATE table1 SET t1_int=4 WHERE t1_prim='one';

错误：输入小于所有的最大值数据库中 t2_int 的值！

UPDATE table1 SET t1_int=6 WHERE t1_prim='two';

错误：输入的值小于数据库中 t2_int 所有值的最大值！

UPDATE table2 SET t2_int=8 WHERE t2_prim1='one' AND t2_prim2=1;

错误：输入的值大于 t1_int 中的值！

UPDATE table2 SET t2_int=11 WHERE t2_prim1='two' AND t2_prim2=2;

错误：输入的值大于 t1_int 中的值！

INSERT INTO table2 VALUES ('one', 3, 6);

错误：输入的值大于 t1_int 中的值！

虽然这些工作得很好：

INSERT INTO table2 VALUES ('one', 3, 6);
UPDATE table2 SET t2_int=1 WHERE t2_prim1='one' AND t2_prim2=1;
UPDATE table1 SET t1_int=8 WHERE t1_prim='two';