Не самые известные сведения о внешних ключах

Владимир Пржиялковский

Mea culpa

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

Речь идет о внешних ключах. Вопросы слушателей заставили недавно освежить мои представления о них. В итоге я

(а) приношу извинения сразу всей своей прошлой аудитории за чересчур категоричные утверждения о поведении внешних ключей в Oracle
(б) привожу в этой статье некоторые свойства внешних ключей, расширяющие их общеизвестные возможности.

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

Определение внешнего ключа

Внешний ключ - это ссылка полей одной таблицы на однотипные поля другой таблицы, обладающие свойством либо (а) уникальности, либо (б) первичного ключа. Исключительно для простоты дальше речь будет идти о простых ключах, то есть состоящих из одного поля. СУБД (в нашем случае - Oracle) обязана следить за тем, чтобы значение внешнего ключа позволило найти запись в таблице, на которую он ссылается (в родительской таблице), а уникальность "родительского поля" гарантирует, что это будет ровно одна запись. Вместе с тем наличия значения в поле внешнего ключа (в подчиненной таблице) не требуется, и автоматические проверки соответствия выполняются только для существующих значений в поле внешнего ключа.

Внешний ключ в Oracle вслед за стандартным SQL реализован как разновидность ограничения. Информация о нем включается в Oracle в системную таблицу USER_CONSTRAINTS.

Внешний ключ в демонстрационной схеме SCOTT в Oracle существует: это поле EMP.DEPTNO, ссылающееся на поле первичного ключа DEPT.DEPTNO. Совпадения имен полей не требуется, но когда оно возможно, это удобно, так как подчеркивает содержательную связь.

Завести внешний ключ можно сразу при создании таблицы, или же потом, командой

ALTER TABLE xxx 
ADD [CONSTRAINT имя] 
FOREIGN KEY (fff) REFERENCING ttt(kkk);

Внешний ключ может ссылаться на поля таблицы из другой схемы

Чаще всего родительская и подчиненные таблицы находятся в одной схеме БД Oracle. Не так известно, что родительская и подчиненная таблицы могут находиться в разных схемах. Так как схемы используются для разграничения доступа, возникает вопрос о полномочиях.

Для того, чтобы иметь возможность сослаться внешним ключом на поле таблицы в другой схеме, на это поле должна иметься привилегия REFERENCING. Особо нужно отметить, что с привилегиями SELECT, INSERT, UPDATE и DELETE привилегия REFERENCING никак не связана. Иными словами схема с подчиненной таблицей может ничего не знать о конкретных значениях ключа, на которые есть возможность ссылаться, равно как на наличие других полей в таблице. Пример:

CONNECT / AS SYSDBA

CREATE USER adam IDENTIFIED BY eva DEFAULT TABLESPACE users;
GRANT CONNECT, RESOURCE TO adam;

CONNECT scott/tiger

GRANT SELECT ON emp TO adam;
GRANT REFERENCES ON dept TO adam;

CONNECT adam/eva

CREATE TABLE emp AS SELECT * FROM scott.emp;
ALTER TABLE emp 
ADD FOREIGN KEY (deptno) REFERENCES scott.dept (deptno);
INSERT INTO emp (ename, deptno) VALUES ('ADAM', 10);
INSERT INTO emp (ename, deptno) VALUES ('EVA', 50);

Последняя вставка будет вызывать ошибку до тех пор, пока в таблице SCOTT.DEPT не появится запись об отделе 50.

(Привилегия SELECT на таблицу EMP была выдана пользователю ADAM исключительно для возможности скопировать эту таблицу).

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

Тем не менее практика баз данных настолько разнообразна, что указанная возможность иногда может оказаться востребованной.

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

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