WITH CHECK OPTION в Oracle SQL — как ограничить вставку и обновление в представлениях

WITH CHECK OPTION в Oracle SQL. Введение

Ключевая фраза WITH CHECK OPTION в Oracle SQL используется при создании представлений, чтобы запретить изменение или вставку строк, не соответствующих условиям самого представления. Это средство контроля данных особенно важно для безопасности и консистентности. В этой статье ты найдёшь 50 примеров использования WITH CHECK OPTION на практике.

🖋️ Синтаксис WITH CHECK OPTION

CREATE VIEW имя_представления AS
SELECT ...
FROM ...
WHERE условие
WITH CHECK OPTION;

Обеспечивает, чтобы любые действия через представление соблюдали его условия
Может быть указано с CONSTRAINT имя_ограничения
Используется только в представлениях

🔄 Где используется WITH CHECK OPTION

Защита от некорректных INSERT/UPDATE через представления
Создание ограничений для бизнес-логики
Использование в многоуровневых представлениях
Сегментация данных по ролям и правам

📊 Примеры использования WITH CHECK OPTION в Oracle SQL (45 примеров)

Простейшее ограничение:

CREATE VIEW v_hr_employees AS
SELECT * FROM employees WHERE department_id = 10
WITH CHECK OPTION;

Ограничение с именем:

CREATE VIEW v_it_employees AS
SELECT * FROM employees WHERE department_id = 20
CONSTRAINT chk_it_dep
WITH CHECK OPTION;

Попытка вставки некорректной строки:

-- Ошибка, если department_id ≠ 10
INSERT INTO v_hr_employees (employee_id, first_name, department_id)
VALUES (205, 'Alice', 99);

Разрешённая вставка:

-- Успешно, строка соответствует WHERE
INSERT INTO v_hr_employees (employee_id, first_name, department_id)
VALUES (206, 'Bob', 10);

Обновление с нарушением условия:

-- Ошибка, нельзя изменить department_id на другой
UPDATE v_hr_employees SET department_id = 30 WHERE employee_id = 101;

Представление с несколькими условиями:

CREATE VIEW v_europe_sales AS
SELECT * FROM sales
WHERE region = 'Europe' AND status = 'Approved'
WITH CHECK OPTION;

Представление с фильтрацией по дате:

CREATE VIEW v_recent_orders AS
SELECT * FROM orders
WHERE order_date >= SYSDATE - 30
WITH CHECK OPTION;

INSERT в представление с правильным значением:

INSERT INTO v_recent_orders (order_id, order_date)
VALUES (999, SYSDATE);

Ошибка при обновлении, нарушающем фильтр:

UPDATE v_recent_orders SET order_date = SYSDATE - 365
WHERE order_id = 999; -- Ошибка

Представление с вложенным SELECT:

CREATE VIEW v_high_salary AS
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
WITH CHECK OPTION;

Представление с фильтром по строковому значению:

CREATE VIEW v_russian_customers AS
SELECT * FROM customers WHERE country = 'Russia'
WITH CHECK OPTION;

Использование псевдонима в представлении:

CREATE VIEW v_dept AS
SELECT department_id AS id, department_name AS name FROM departments
WHERE location_id = 1700
WITH CHECK OPTION;

Обновление записи с соблюдением условия:

UPDATE v_dept SET name = 'HR & Admin' WHERE id = 10;

Представление с вложенным CASE:

CREATE VIEW v_classified AS
SELECT employee_id, salary,
  CASE WHEN salary > 10000 THEN 'HIGH'
       WHEN salary > 5000 THEN 'MID'
       ELSE 'LOW' END AS level
FROM employees
WHERE department_id = 30
WITH CHECK OPTION;

Представление READ ONLY без CHECK OPTION:

CREATE VIEW v_static AS
SELECT * FROM employees WHERE department_id = 40
WITH READ ONLY;

Представление с CHECK OPTION и TO_DATE:

CREATE VIEW v_future_orders AS
SELECT * FROM orders WHERE delivery_date > TO_DATE('2024-12-31', 'YYYY-MM-DD')
WITH CHECK OPTION;

Представление с ограничением по email-домену:

CREATE VIEW v_company_users AS
SELECT * FROM users
WHERE email LIKE '%@company.com'
WITH CHECK OPTION;

Представление с NULL-проверкой:

CREATE VIEW v_with_managers AS
SELECT * FROM employees WHERE manager_id IS NOT NULL
WITH CHECK OPTION;

Представление по дню недели:

CREATE VIEW v_weekend_sales AS
SELECT * FROM sales
WHERE TO_CHAR(sale_date, 'DY') IN ('SAT', 'SUN')
WITH CHECK OPTION;

Представление с фильтрацией по JSON_VALUE:

CREATE VIEW v_active_json AS
SELECT * FROM users_json
WHERE JSON_VALUE(data, '$.active') = 'true'
WITH CHECK OPTION;

Представление по статусу и дате:

CREATE VIEW v_pending_recent AS
SELECT * FROM requests
WHERE status = 'PENDING' AND created_at > SYSDATE - 7
WITH CHECK OPTION;

Вставка через представление с проверкой:

INSERT INTO v_pending_recent (request_id, status, created_at)
VALUES (1001, 'PENDING', SYSDATE);

Представление с аналитикой невозможно обновить:

CREATE VIEW v_ranked AS
SELECT employee_id, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WITH CHECK OPTION; -- не будет работать, RANK() не обновляемое

Представление с GROUP BY:

CREATE VIEW v_dept_counts AS
SELECT department_id, COUNT(*) AS cnt FROM employees
GROUP BY department_id
WITH CHECK OPTION; -- приведёт к ошибке

Представление с SELECT из представления:

CREATE VIEW v_nested AS
SELECT * FROM v_hr_employees WHERE salary > 5000
WITH CHECK OPTION;

Представление с WHERE IN:

CREATE VIEW v_special_deps AS
SELECT * FROM employees
WHERE department_id IN (10, 20, 30)
WITH CHECK OPTION;

Представление с BETWEEN:

CREATE VIEW v_mid_salary AS
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000
WITH CHECK OPTION;

Представление с REGEXP_LIKE:

CREATE VIEW v_email_filter AS
SELECT * FROM users
WHERE REGEXP_LIKE(email, '^admin')
WITH CHECK OPTION;

Представление с TO_CHAR:

CREATE VIEW v_formatted_date AS
SELECT employee_id, TO_CHAR(hire_date, 'YYYY-MM') AS hire_month FROM employees
WHERE hire_date > SYSDATE - 365
WITH CHECK OPTION;

Представление с SIGN:

CREATE VIEW v_above_avg AS
SELECT employee_id, salary, SIGN(salary - 6000) AS status FROM employees
WHERE salary > 6000
WITH CHECK OPTION;

Представление с CURRENT_DATE:

CREATE VIEW v_today_logins AS
SELECT * FROM logins WHERE login_time >= CURRENT_DATE
WITH CHECK OPTION;

Представление с LPAD:

CREATE VIEW v_padded_ids AS
SELECT LPAD(employee_id, 5, '0') AS emp_id FROM employees
WHERE department_id = 60
WITH CHECK OPTION;

Представление с ограничением по стране:

CREATE VIEW v_local_customers AS
SELECT * FROM customers WHERE country = 'Россия'
WITH CHECK OPTION;

Представление с TRIM:

CREATE VIEW v_trimmed AS
SELECT TRIM(first_name) AS name FROM employees WHERE department_id = 50
WITH CHECK OPTION;

Представление с пользовательской функцией:

-- Не обновляемое, только для чтения
CREATE VIEW v_custom_fn AS
SELECT my_func(employee_id) FROM employees
WITH CHECK OPTION;

Представление с IS NULL:

CREATE VIEW v_no_manager AS
SELECT * FROM employees WHERE manager_id IS NULL
WITH CHECK OPTION;

Представление с аналитикой DENSE_RANK:

CREATE VIEW v_dense_rank AS
SELECT employee_id, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
WITH CHECK OPTION;

Представление по результатам SUBSTR:

CREATE VIEW v_initials AS
SELECT SUBSTR(first_name, 1, 1) AS initial FROM employees
WHERE department_id = 20
WITH CHECK OPTION;

Представление с LTRIM:

CREATE VIEW v_ltrim AS
SELECT LTRIM(first_name) AS clean_name FROM employees
WHERE department_id = 90
WITH CHECK OPTION;

Представление с ограничением по количеству символов:

CREATE VIEW v_short_names AS
SELECT * FROM users WHERE LENGTH(username) <= 10
WITH CHECK OPTION;

Представление на основе SELECT DISTINCT:

-- Не обновляемое
CREATE VIEW v_unique_emails AS
SELECT DISTINCT email FROM users
WITH CHECK OPTION;

Представление с фильтрацией IN JSON:

CREATE VIEW v_json_region AS
SELECT * FROM regions_json
WHERE JSON_VALUE(data, '$.zone') = 'East'
WITH CHECK OPTION;

Представление с FETCH:

CREATE VIEW v_first_10 AS
SELECT * FROM employees FETCH FIRST 10 ROWS ONLY
WITH CHECK OPTION;

Представление с логикой CASE в WHERE:

CREATE VIEW v_case_filter AS
SELECT * FROM employees
WHERE (CASE department_id WHEN 10 THEN 1 ELSE 0 END) = 1
WITH CHECK OPTION;

Представление с CHECK OPTION и CHECK CONSTRAINT:

CREATE VIEW v_hr AS
SELECT * FROM employees WHERE department_id = 10
CONSTRAINT chk_hr_only WITH CHECK OPTION;

📆 Заключение: зачем использовать WITH CHECK OPTION

WITH CHECK OPTION — это простая и мощная техника защиты бизнес-правил на уровне представления. Она гарантирует, что через VIEW нельзя добавить или изменить данные, нарушающие его фильтр. Особенно полезно в многопользовательских системах, где разные роли видят разные подмножества данных.