Не найвідоміші відомості про зовнішні ключі

Зміст



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 слід уникати.


Додаткова інформація


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*