Статьи

Удивительный трюк SQL: ограничения на представления

Январь 14, 2019

Ограничения CHECK уже достаточно велики, когда вы хотите очистить данные. Но существуют некоторые ограничения для ограничений CHECK , в том числе тот факт, что они применяются к самой таблице, когда иногда требуется указать ограничения, которые применяются только в определенных ситуациях.

Это можно сделать с помощью стандартного предложения WITH CHECK OPTION стандарта SQL, которое реализовано по крайней мере в Oracle и SQL Server. Вот как это сделать:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE books (
  id    NUMBER(10)         NOT NULL,
  title VARCHAR2(100 CHAR) NOT NULL,
  price NUMBER(10, 2)      NOT NULL,
   
  CONSTRAINT pk_book PRIMARY KEY (id)
);
/
 
CREATE VIEW expensive_books
AS
SELECT id, title, price
FROM books
WHERE price > 100
WITH CHECK OPTION;
/
 
INSERT INTO books
VALUES (1, '1984', 35.90);
 
INSERT INTO books
VALUES (
  2,
  'The Answer to Life, the Universe, and Everything',
  999.90
);

Как видите, expensive_books книги — это все те книги, цена которых превышает 100,00. Это представление сообщит только о второй книге:

1
SELECT * FROM expensive_books;

Вышеприведенный запрос дает:

1
2
3
ID TITLE                                       PRICE
-- ----------------------------------------- -------
 2 The Answer to Life, the Universe, and ...   999.9

Но теперь, когда у нас есть эта опция CHECK, мы также можем запретить пользователям вставлять «дорогие книги», которые на самом деле не дороги. Например, давайте запустим этот запрос:

1
2
INSERT INTO expensive_books
VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);

Этот запрос не будет работать сейчас. Мы получаем:

1
ORA-01402: view WITH CHECK OPTION where-clause violation

Мы также не можем обновить ни одну из «дорогих книг», чтобы она была недорогой:

1
2
UPDATE expensive_books
SET price = 9.99;

Этот запрос приводит к тому же сообщению об ошибке ORA-01402.

Встроенный с опцией проверки

Если вам нужно локально предотвратить вставку фиктивных данных в таблицу, вы также можете использовать встроенные предложения WITH CHECK OPTION например:

1
2
3
4
5
6
7
INSERT INTO (
  SELECT *
  FROM expensive_books
  WHERE price > 1000
  WITH CHECK OPTION
) really_expensive_books
VALUES (3, 'Modern Enterprise Software', 999.99);

И вышеупомянутый запрос снова приводит к ошибке ORA-01402.

Использование преобразования SQL для создания специальных ограничений

Несмотря на то, что CHECK OPTION очень полезен для хранимых представлений, которые могут иметь надлежащие разрешения для тех пользователей, которые могут не иметь прямого доступа к базовой таблице, встроенный параметр CHECK OPTION в основном полезен, когда вы преобразуете динамический SQL в промежуточный уровень преобразования SQL в вашем приложении.

Это можно сделать, например, с помощью возможностей преобразования SQL в jOOQ , где вы можете следить за определенной таблицей в ваших инструкциях SQL, а затем централизованно предотвращать выполнение фиктивного DML. Это отличный способ реализовать многопользовательский режим, если ваша база данных изначально не поддерживает безопасность на уровне строк.

Следите за будущим сообщением в блоге, объясняющим, как преобразовать ваш SQL с помощью jOOQ для обеспечения безопасности на уровне строк для любой базы данных.

Ссылка: Удивительный трюк с SQL: ограничения на представления от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
41
Share: