Блог

Интересно, как решить эту проблему.

Я получил две таблицы

Table1 (t1_prim, t1_int) с t1_prim в качестве первичного ключа и t1_int не null .

Table2 (t2_prim1, t2prim2, t2_int) с t2_prim1 и t2_prim2 в качестве первичного ключа, а ограничение внешнего ключа t2_prim1 ссылается на t1_prim.

как я могу определить ограничение, которое гарантирует, что никто не вводит целое число в t2_int, которое больше, чем соответствующая запись в t1_int ?

Я попробовал это так (не работает, потому что вы не можете вводить подзапросы в check constraint):

 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?

Наконец-то нашел ответ на мой вопрос. Вы можете решить проблему с помощью триггеров. Вот рабочий пример:

 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';