Материал: Базы данных. Лабораторная работа 7

Федеральное агентство связи ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ

ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ «САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ТЕЛЕКОММУНИКАЦИЙ ИМ. ПРОФ. М. А. БОНЧ-БРУЕВИЧА» (СПбГУТ)

Факультет инфокоммуникационных сетей и систем Кафедра фотоники и линий связи

ЛАБОРАТОРНАЯ РАБОТА №7 по дисциплине «Базы данных»

на тему «Целостность данных, триггеры в MySQL»

Выполнили: студенты 3-го курса дневного отделения группы ИКПИ-85 Ежуров Антон Павлович

Коваленко Леонид Александрович Преподаватель:

ассистент кафедры ПИиВТ Овчинников Антон Олегович

Санкт-Петербург

2020

Цель работы Получить практические навыки в обеспечении целостности базы

данных MySQL с использованием триггеров. Ход работы

Создадим базу данных university в программе-дизайнере MySQL Workbench. В этой базе данных создадим таблицу users с полями:

1.id типа INT, первичный ключ (PK), счетчик (AI);

2.name типа VARCHAR, ненулевое (NN);

3.is_update типа BOOLEAN, ненулевое (NN), по умолчанию FALSE.

Для таблицы users создадим триггер (вкладка Triggers) (табл. 1). Таблица 1 — Триггер для таблицы users базы данных university

CREATE DEFINER = CURRENT_USER TRIGGER `university`.`users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW

BEGIN

INSERT INTO results SET laboratory=false, examination=0, user_id=NEW.id;

END

В этой базе данных создадим таблицу results с полями:

1.id типа INT, первичный ключ (PK), счетчик (AI);

2.laboratory типа BOOLEAN, ненулевое (NN);

3.examination типа INT, ненулевое (NN);

4.user_id типа INT, ненулевое (NN).

Для таблицы results создадим внешний ключ (вкладка Foreign Keys) с именем user_id на таблицу university.users, с колонки user_id (таблицы results) на колонку id (таблицы users). В качестве события ON DELETE укажем CASCADE.

Для таблицы results создадим триггер (вкладка Triggers) (табл. 2). Таблица 2 — Триггер для таблицы results базы данных university

CREATE DEFINER = CURRENT_USER TRIGGER `university`.`results_AFTER_UPDATE` AFTER UPDATE ON `results` FOR EACH ROW

BEGIN

UPDATE users SET is_update=true WHERE id = NEW.user_id;

END

Сохраним созданную в программе-дизайнере схему базы данных на локальный компьютер.

2

Запустим генерацию базы данных на сервере MySQL (Пункт меню: Database → Forward Engineer). В опциях поставим галки напротив пунктов

Generate INSERT statements for tables и DROP objects before each CREATE object.

mysql -u root -p) и

активизируем базу данных university (команда use university).

В командной строке выполним следующие команды (рис. 1, 2, 3, 4):

UPDATE results SET laboratory=true, examination=5 WHERE user_id =

2;

SELECT * FROM results WHERE user_id = 2; SELECT * FROM users;

Рисунок 1 — Результат выполнения первой части команд

3

Рисунок 2 — Результат выполнения второй части команд

Рисунок 3 — Результат выполнения третьей части команд

Рисунок 4 — Результат выполнения четвертой части команд 4

Выводы

Внешний ключ SQL — это ключ, используемый для установления связи между таблицами. Иногда его также называют ссылочным ключом. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.

Поддержка внешних ключей также называется соблюдением ссылочной целостности (корректности значений внешних ключей). Реляционные СУБД поддерживают автоматический контроль ссылочной целостности. Любая операция, изменяющая данные в таблице, вызывает автоматическую проверку ссылочной целостности. При этом:

1.При операции добавления записи автоматически проверяется, ссылаются ли внешние ключи в этой записи на существующие записи в заявленных таблицах. Если выясняется, что операция приведёт к появлению некорректных ссылок, она не выполняется — система возвращает ошибку.

2.При операции редактирования записи:

1)Если изменяется её первичный ключ и на данную запись имеются ссылки, то операция редактирования завершается с ошибкой;

2)Если изменяется какой-то из внешних ключей, хранящихся в этой записи, и после изменения внешний ключ будет ссылаться на несуществующую запись, то операция редактирования завершается с ошибкой.

3. При операции удаления записи проверяется, нет ли на неё ссылок. Если ссылки имеются, то возможно три варианта дальнейших действий (фактически выполняемый зависит от СУБД и от выбора программиста, который он должен сделать при описании связи):

1)Запрет — удаление блокируется и возвращается ошибка.

2)Каскадное удаление — в одной транзакции производится удаление данной записи и всех записей, ссылающихся на данную. Если на удаляемые записи также есть ссылки и настройки также требуют удаления, то каскадное удаление продолжается дальше. Таким образом, после удаления данной

5