Базы данных Oracle - статьи

         

Удаление родительской записи может автоматически изменять подчиненные таблицы


Классическим проявлением ограничения "внешний ключ" является отказ СУБД удалить родительскую запись при наличии хотя бы одной подчиненной. В этом легко убедиться, войдя в схему SCOTT и набрав там

DELETE FROM dept WHERE deptno = 10;

Однако Oracle позволяет смоделировать и иную реакцию СУБД, все-таки разрешив удаление родительской записи. Для этого при создании внешнего ключа нужно специально указать фразу ON DELETE.

Указание ON DELETE CASCADE приведет к автоматическому удалению подчиненных записей:

CREATE TABLE x(a NUMBER PRIMARY KEY); CREATE TABLE y(b NUMBER PRIMARY KEY, c NUMBER REFERENCES x(a) ON DELETE CASCADE);

INSERT INTO x VALUES (1); INSERT INTO y VALUES (2,1); DELETE FROM x; SELECT * FROM y;

При этом автоматическое удаление может распространяться по цепочке:

CREATE TABLE z(d NUMBER PRIMARY KEY, e NUMBER REFERENCES y(b) ON DELETE CASCADE);

INSERT INTO x VALUES (1); INSERT INTO y VALUES (2, 1); INSERT INTO z VALUES (3, 2); DELETE FROM x; SELECT * FROM z;

(Автоматическим удалением по цепочке следует пользоваться с особой осторожностью).

Указание ON DELETE SET NULL приведет к автоматическому удалению значений в полях-ссылках подчиненных записей:

CREATE TABLE w(f NUMBER REFERENCES z(d) ON DELETE SET NULL);

INSERT INTO z VALUES (3, NULL); INSERT INTO w VALUES (3); DELETE FROM z; SELECT * FROM w;

Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа "внешний ключ", и не трогая значений подчиненных записей:

INSERT INTO x VALUES (1); INSERT INTO y VALUES (2, 1); DROP TABLE x CASCADE CONSTRAINTS; SELECT * FROM y;

DROP TABLE y CASCADE CONSTRAINTS; DROP TABLE z CASCADE CONSTRAINTS; DROP TABLE w CASCADE CONSTRAINTS;

Попутно обратите внимание, что если бы в предложениях DROP выше не фигурировала фраза CASCADE CONSTRAINTS, удалять таблицы пришлось бы в строго определенном порядке. Но это же обеспечивает в общем более "чистые" данные в БД, так что как правило фразы CASCADE CONSTRAINTS следует избегать.



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