Главная | Обратная связь | Поможем написать вашу работу!

Контроль цілісності даних з використанням тригерів

Вище вже не раз підіймалися питання про зв'язки, які існують між деякими полями таблиць в БД. Наприклад, поле SNUM таблиці STUDENTS відповідає полю SNTUM в таблиці USP, а поле PNUM таблиці PREDMET - відповідно PNUM в тій же таблиці USP. Такий зв'язок можна назвати довідковою цілісністю - вона необхідна для того, щоб уникнути надмірності інформації в таблицях. Розглянемо використовування цієї можливості більш докладно.

Нагадаємо: коли всі значення в одному полі таблиці представлені в полі інший, звичайно говорять: перше поле посилається на друге, і це указує на прямий зв'язок між значеннями двох полів. Наприклад, кожний запис з інформацією про студента в таблиці STUDENTS має поле SNUM. яке указує на оцінку, збережену в таблиці успішності USP.

Коли одне поле в таблиці посилається на інше, воно називається зовнішнім ключем, а поле, на яке воно посилається батьківським ключем. Таким чином, поле SNUM таблиці STUDENTS є батьківським ключем, а поле SNUM в таблиці USP - зовнішнім ключем. Імена зовнішнього ключа і батьківського ключа не обов'язково повинні бути однаковими, проте дотримання умови ідентичності імен робить з'єднання більш зрозумілим.

Крім того, зовнішній ключ не обов'язково повинен полягати тільки з одного поля, хоча ключі, що складаються з одного поля, зустрічаються частіше всього. Подібно первинному ключу, зовнішній ключ може мати будь-яке число полів, які всі разом обробляються як єдине ціле. Зовнішній ключ і батьківський ключ, на який він посилається, обов'язково повинні мати однаковий тип поля, і, при використовуванні декількох полів, знаходитися в однаковому порядку.

Коли поле є зовнішнім ключем, очевидне, що воно певним чином пов'язане з таблицею, на яку посилається. Фактично відбувається наступне: кожному значенню в полі, яке є зовнішнім ключем, безпосередньо відповідає значення в іншому полі - батьківському ключі. При цьому кожне значення зовнішнього ключа повинно однозначно посилатися до одного значення батьківського ключа, тоді можна говорити про те, що система знаходиться в стані довідкової цілісності.

Якщо це не так, то виникає неоднозначна ситуація. Наприклад, якщо в таблиці STUDENTS в полі SNUM з'явиться два однакові значення (скажімо, 3412), то при отриманні результатів запиту про отримані оцінки

SELECT STUDENTS.SFAM, STUDENTS.SIMA

STUDENTS.SOTCH, USP.OCENKA FROM STUDENTS, USP WHERE STUDENTS.SNUM = USP.SNUM AND STUDENTS.SNUM = 3412;

буде неясне, якому саме студенту ця оцінка належить. З іншого боку, якщо в таблиці успішності USP з'явиться запис із значенням поля SNUM = 3417, яке відсутнє в таблиці STUDENTS, то в результаті запиту

SELECT STUDENTS.SFAM, STUDENTS.S IMA STUDENTS.SOTCH, USP.OCENKA FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM AND USP.SNUM = 3417;

можна отримати список оцінок, які не належать жодному студенту. Очевидно, що такі ситуації будуть виключені, якщо кожне значення в зовнішньому ключі буде представлене тільки один раз в батьківському ключі, і це значення повинне існувати.

SQL автоматично підтримує довідкову цілісність даних з обмеженням FOREIGN KEY. Ця функція обмежує значення, які можна ввести в БД так, щоб примусити зовнішній ключ і батьківський ключ відповідати принципу довідкової цілісності. Одна з дій цього обмеження значень для полів, обмежених як зовнішній ключ, які відсутні в батьківському ключі. До того ж FOREIGN KEY також впливає на можливість користувача змінювати або видаляти значення батьківського ключа.

Обмеження FOREIGN KEY використовується в командах CREATE TABLE або ALTER TABLE, які оголошують зовнішнім ключем відповідне поле таблиці, при цьому дається ім'я ключу, на яке передбачається посилання усередині цього обмеження. FOREIGN KEY використовується аналогічно тому, як це реалізовувалося для інших типів обмежень, розглянутих вище: воно може бути обмеженням поля або таблиці, причому в останньому випадку - дозволяє використати декілька полів в якості одного зовнішнього ключ. • Синтаксис обмеження таблиці FOREIGN KEY наступний:

FOREIGN KEY <COLUMN LIST> REFERENCES <PKTABLE> [<COLUMN LIST>]

Тут перший список полів - це перелік розділених комами з одного або більш полів створюваної або модифікується таблиці. PKTABLE - це таблиця, що містить батьківський ключ. Інший список полів - це поля, які будуть складати батьківський ключ.

Списки двох полів повинні бути сумісні, тобто повинні виконуватися наступні умови:

• 1. списки повинні містити однакову кількість полів;

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

Для прикладу створимо таблицю успішності USP з полем SNUM, визначеним як зовнішній ключ, що посилається на таблицю студентів STUDENTS:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL, FOREIGN KEY (SNUM)

REFERENCES STUDENTS (SNUM));

Звернете увагу на те, що при використовуванні ALTER TABLE замість CREATE TABLE для реалізації обмеження FOREIGN KEY значення, які указуються в зовнішньому і батьківському ключах, повинні бути в стані довідкової цілісності. Інакше команда відхилюватиме. Звідси витікає, що вживання команди ALTER TABLE приводить до необхідності кожного разу стежити за дотриманням структурних принципів довідкової цілісності.

Варіант обмеження поля FOREIGN KEY часто називають посилальним обмеженням REFERENCES, оскільки воно фактично не містить в собі ключових слів FOREIGN KEY, а просто використовує слово REFERENCES, як це показано в наступному прикладі:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS (SNUM),

PNUM INTEGER NOT NULL);

В даному випадку USP.SNUM визначається як зовнішній ключ, в якого батьківським є STUDENTS.SNUM.

Взагалі, використовуючи обмеження FOREIGN KEY таблиці або поля, можна не вказувати список полів батьківського ключа, якщо батьківський ключ має обмеження PRIMARY KEY. При цьому, у разі вживання ключів з багатьма полями, обов'язкове виконання умови, щоб порядок полів в зовнішніх і первинних ключах співпадав.

Наприклад, якщо в таблиці STUDENTS поле SNUM є PRIMARY KEY. то його можна використати як зовнішній ключ в таблиці успішності аналогічно попередньому прикладу таким чином:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS,

PNUM INTEGER NOT NULL);

Підтримка довідкової цілісності вимагає обмежень і на значення, які можуть присутній в полях, оголошених як зовнішній ключ і батьківський ключ. Це означає, зокрема, що батьківський ключ повинен бути унікальним і не містити NULL значень. З урахуванням цього, при оголошенні FOREIGN KEY, SQL повинен бути упевненим, що подвійні або NULL значення не присутні в батьківському ключі. Отже, користувач повинен переконатися в тому, що всі поля, які використовуються в якості батьківських ключів, мають обмеження PRIMARY KEY або UNIQUE і NOT NULL.

Рекомендованою стратегією при створенні структури БД є посилання зовнішніх ключів таблиць тільки на первинні ключі. Коли використовуються зовнішні ключі, відбувається зв'язок не просто з батьківськими ключами, на які вони посилаються, - зв'язуються певні записи таблиць. Дійсно, сам по собі батьківський ключ не забезпечує ніякої інформації, яка б не була б вже уявлена в зовнішньому ключі. Наприклад, сенс поля SNUM в ролі зовнішнього ключа полягає в тому. що він забезпечує зв'язок, але не до значень полів SNUM, деякі він посилається, а до іншої інформації, що знаходиться в тому ж записі, наприклад, до прізвища студента.

Оскільки основною задачею первинного ключа є ідентифікація унікального запису таблиці, то це - найлогічніший і менш неоднозначний вибір для зовнішнього ключа. Зовнішній ключ, який не має ніякої іншої цілі, окрім скріплення рядків таблиць, в цьому сенсі нагадує первинний ключ, використовуваний виняткове для ідентифікації рядків, і є добрим засобом для збереження грамотної і простої структури БД.

З вищевикладеного логічно витікає, що зовнішній ключ, взагалі кажучи, повинен містити тільки ті значення, які фактично є в батьківському ключі, або NULL значення. Спроба ввести інші дані в цей ключ відхилюватиме системою.

Якщо оголосити зовнішній ключ, як NOT NULL, то від невизначених значень в ньому можна позбутися, проте це необов'язково, а іноді небажане. Це може відбутися за ситуації, коли, наприклад, вводиться оцінка, а номер студентського квитка невідомий наперед.

Нагадаємо, що в якості зовнішнього ключа може виступати комбінація з декількох полів. Щоб створити такий зовнішній ключ, необхідно пам'ятати про те. що поєднання цих полів в батьківській таблиці повинне відповідати вимозі обмеження UNIQUE. Створення зовнішнього ключа у такий спосіб підтримує цілісність БД, навіть якщо при цьому користувачу буде заборонено внутрішнє переривання (виключення) цілісності БД.

З погляду реалізації цілісності БД внутрішні переривання украй небажані, і. якщо вони в системі припускають, то часто цілісність даних доведеться підтримувати уручну.

Розглянуті обмеження впливають на можливість (або неможливість) виконання команд модифікації даних. Для полів, визначених як зовнішні ключі, будь-які значення, що поміщаються командами INSERT або UPDATE, повинні бути вже представлені в їхніх батьківських ключах. Більше того, припускає вставляти в ці поля NULL значення, не дивлячись на те. що вони не дозволені в батьківських ключах. Крім цього, можна видаляти командою DELETE будь-які записи із зовнішніми ключами, не використовуючи батьківські ключі взагалі.

Відповідно до стандарту, зміна або видалення значень батьківського ключа взагалі не припускає. Це, наприклад, означає, що не можна видалити дані про студента з таблиці STUDENTS доти. поки в таблиці успішності USP для нього є яка-небудь інформація. З одного боку, це є позитивним моментом - неможлива ситуація, коли з'являться оцінки, не належні жодному студенту. З другого боку, достатньо часто виникає необхідність в повному видаленні інформації, наприклад, у разі відрахування студента з вузу. Про ці можливості ми вже згадували вище, кажучи про каскадування і обмеженні дій.

Отже, поведінка системи при зміні батьківського ключа може відрізнятися від передбаченого стандартом. Якщо необхідно змінити або видалити поточне посилальне значення батьківського ключа, є три можливості:

• заборонити зміни, звівши наклеп, що зміни в батьківському ключі обмежені;
• зробивши зміни в батьківському ключі, виробити зміни і в зовнішньому ключі автоматично, що фактично є каскадною зміною;
• зробити зміну в батьківському ключі і встановити зовнішній ключ в NULL значення автоматично, що називається порожньою зміною зовнішнього ключа.

В межах цих можливостей можна обробляти всі команди модифікації. З командою INSERT, правда, в цьому випадку ситуація дещо складніше, до. при її використовуванні поміщаються нові значення батьківського ключа в таблицю. Таким чином, то, що трапиться в батьківському ключі, можна розділити на обмежені (RESTRICTED), каскадують (CASCADES) і порожні (NULL) зміни.

Для прикладу розглянемо те, як ці можливості застосовуються в таблицях. Припустимо, що є необхідність в зміні номера студентського квитка, причому оцінки повинні зберегтися в цього студента з новим номером. Якщо ж дані про студента віддаляються, то необхідно, щоб його оцінки в таблиці успішності залишалися, скажімо, для подальшого звіту. Щоб це реалізувати, необхідно вказати умову UPDATE з каскадувати, DELETE з обмеженим ефектом.

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS,

PNUM INTEGER NOT NULL,

UPDATE STUDENTS CASCADES, DELETE STUDENTS RESTRICTED);

Після цього, при видаленні даних про студента з таблиці STUDENTS, команда не буде виконана до тих пір, поки не будуть видалені його оцінки або не зміниться значення поля SNUM. З другого боку, якщо номер студентського квитка буде змінений, то для відповідних оцінок цього студента значення поля SNUM також автоматично поміняється.

Для демонстрації NULL змін скористаємося таким прикладом:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE

SNUM INTEGER REFERENCES STUDENTS,

PNUM INTEGER NOT NULL, DELETE STUDENTS NULLS);

В цьому випадку видалення інформації про студента з таблиці STUDENTS пройде успішно, а записи, відповідні його оцінкам, будуть мати в полі SNUM NULL значення, що може стати в нагоді, скажімо, для розрахунку середньої оцінки по учбових предметах за семестр. Зрозуміло, за наявності в таблиці DELETE з NULL ефектом обмеження NOT NULL не повинно бути в полі SNUM.

Кажучи про зовнішні ключі, ми вже згадували про можливість їхнього посилання на ту ж таблицю. Інакше кажучи, батьківська і зовнішня таблиця в даному випадку - одне і те саме.

Ця особливість обмеження FOREIGN KEY може стати в нагоді, наприклад, в наступному випадку. Хай структура таблиці STUDENTS буде дещо змінена (див. табл. 2.7). Назвемо отриману таблицю STUDENTS2. В таблиці STUDENTS2 з'явилося поле STAR, яке містить номер студентського квитка старости. В нашому випадку студент Нагірний є старостою всього потоку, йому підкоряється староста Гріценко, якому, у свою чергу, підкоряються всі інші. Очевидно, оскільки староста сам є студентом, то він теж уявлений в цій таблиці.

Таблиця 2.7 Модифікована таблиця STUDENTS

STUDENTS2
SNUM	SFAM	SIMA	SOTCH	STAR
	Поляків	Анатолій	Олексійович
	Старова	Любов	Михайлівна
	Гріценко	Владимир	Миколайович
	Котеняті	Анатолій	Миколайович
	Нагірний	Євгеній	Васильович	NULL

Спробуємо створити таку таблицю, причому номер студентського квитка, в полі SNUM буде первинним ключем, а на нього буде посилатися зовнішній ключ, в якості якого оголосимо поле STAR:

CREATE TABLE STUDENTS2

(SNUM INTEGER NOT NULL PRIMARY KEY

SFAM CHAR (20) NOT NULL

SIMA CHAR (10)

SOTCH CHAR (15)

STAR INTEGER REFERENCES STUDENTS2);

З цього прикладу видно, що кожний із студентів посилається на іншого як на свого старосту, проте Нагірний, як самий старший, повинен мати в полі ST AR NULL значення, тобто. це поле повинне припускати невизначені значення. Це вимушує зробити принцип довідкової цілісності - в іншому випадку в таку таблицю не можна було б додати жодному запису. Навіть якщо староста посилався б на самого себе,.в момент вставки найпершого запису в таблицю студент з таким номером повинен вже існувати, інакше команда вставки б відхилювала. Більше того, якщо зовнішній ключ таблиці посилається на саму себе не напряму, а через посилання до іншої таблиці (наприклад, зовнішнім ключем в STUDENTS2 була б таблиця USP. а в неї. у свою чергу. - таблиця STUDENTS2), цей принцип повинен бути збережений. Такий підхід називається перехресним посиланням. SQL підтримує цю можливість, але практично це може викликати труднощі, оскільки будь-яка таблиця з цих двох, створена попередній, є посилальною таблицею, для ще не існуючої іншої таблиці. Рекомендується бути дуже обережним і акуратним при використовуванні перехресних посилань, до. механізм модифікації і видалення даних може пошкодити інформацію, що зберігається в БД.

ЛЕКЦІЯ№

⇐ Предыдущая 123 4 5 6 7 8 Следующая ⇒