Пример нарушения ограничения

Из всей информации, предоставляемой оператором GET DIAGNOSTICS, самой важной является информация о нарушении ограничения. Рассмотрим пример. Создана таблица EMPLOYEE (сотрудник) со столбцами ID (идентификатор) и Salary (зарплата):

CREATE TABLE EMPLOYEE

(ID CHAR(5) CONSTRAINT EmpPK PRIMARY KEY,

Salary DEC(8,2) CONSTRAINT EmpSal CHECK Salary > 0,

Dept CHAR(5) CONSTAINT EmpDept

REFERENCES DEPARTMENT) ;

Кроме того, имеется таблица DEPARTMENT (отдел) со столбцами DeptNo (номер отдела) и Budget (бюджет):

CREATE TABLE DEPARTMENT

(DeptNo CHAR(5),

Budget DEC(12,2) CONSTRAINT DeptBudget

CHECK(Budget >= SELECT SUM(Salary) FROM EMPLOYEE

WHERE EMPLOYEE.Dept=DEPARTMENT.DeptNo),

...) ;

А теперь посмотрите на следующий оператор INSERT:

INSERT INTO EMPLOYEE VALUES(:ID_VAR, :SAL__VAR, :DEPT_VAR);

Предположим, что вы получили значение SQLSTATE, равное '23000'. Посмотрев в документацию по SQL, вы видите, что этому значению соответствует описание "нарушение ограничения целостности". Это означает, что имеет место одна из следующих ситуаций.

Значение IDJVAR повторяет уже существующее значение ID, т.е. нарушено ограничение PRIMARY KEY.

Значение SALJVAR отрицательное — иначе говоря, нарушено ограничение CHECK на столбце Salary.

Значение DEPT_VAR не является правильным ключом, соответствующим какой-либо из строк таблицы DEPARTMENT, так что нарушено ограничение REFERENCES в столбце Dept.

Значение SAL_VAR настолько большое, что у сотрудников, работающих в отделе, для которого вводятся новые данные, сумма окладов превышает значение Budget для этого отдела. На этот раз имеется нарушение ограничения CHECK в столбце Budget таблицы DEPARTMENT. (Вспомните, что при изменении базы данных должны проверяться все связанные с ним ограничения, а не только те, которые определены в изменяемых таблицах.)

Обычно, чтобы узнать причины невыполнения оператора INSERT, приходится проводить большое количество тестов. Но на этот раз все, что нужно, можно узнать с помощью команды GET DIAGNOSTICS:

DECLARE ConstNameVar CHAR(18) ;

GET DIAGNOSTICS EXCEPTION 1

ConstNameVar = CONSTRAINT_NAME ;

И если значение SQLSTATE равно '23000', то этот оператор GET DIAGNOSTICS присваивает переменной ConstNameVar одно из следующих значений: 'EmpPK', 'EmpSal', 'EmpDept' или 'DeptBudget'. Обратите внимание, для того, чтобы однозначно определить ограничение CONSTRAINT_NAME, еще могут понадобиться значения элементов CONSTRAINT_SCHEMA и CONSTRAINT_CATALOG.

Содержание раздела