CONSTRAINT: целостность базы данных

Контроль целостности базы данных

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

Что такое согласованность данных, когда мы говорим о базе данных?

Давай возьмем наш интернет-магазин с таблицами employee, product и task. Мы уже знаем, что в таблице task могут быть задачи, не назначенные ни на кого: employee_id у таких строк равен NULL.

А вот что будет, если в таблице task будет запись с employee_id равным, допустим, 115? Ведь у нас нет такого сотрудника. У нас нет сотрудника с id = 115 в таблице employee. В то же время ссылка на сотрудника с таким id есть в таблице task. Это и есть пример несогласованности данных.

Так как же нам согласовать эти данные? Идеально было бы сделать так, что SQL-сервер при любом изменении данных контролировал все эти нюансы. И такая возможность есть, называется она FOREIGN_KEY.

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

Добавление FOREIGN KEY

Такой ключ можно добавлять в таблицу как на этапе ее создания, так и после, с помощью ALTER TABLE. Формат принципиально не отличается. Мы приведем оба варианта.

Общий вид такого ключа/правила имеет вид:

FOREIGN KEY (колонка) REFERENCES таблица(колонка)

Давай добавим в таблицу task этот ключ/правило, чтобы гарантировать, что все employee_id из таблицы ссылаются на существующую запись в таблице employee. Выглядеть этот скрипт будет так:

ALTER TABLE task ADD FOREIGN KEY (employee_id) REFERENCES employee(id)

А если бы мы решили добавить это правило в момент создания таблицы task, то код выглядел бы так:

CREATE TABLE task ( id INT, name VARCHAR(100), employee_id INT, deadline DATE, PRIMARY KEY (id), FOREIGN KEY (employee_id) REFERENCES employee(id) );

Кстати, бывают ситуации, когда строка, на которую мы ссылаемся, имеет уникальный составной ключ: например “ФИО и год рождения” или “productCatogoryId и productId”. Тогда FOREIGN KEY можно записать так:

FOREIGN KEY (наша_колонка1, наша_колонка2) REFERENCES таблица(их_колонка1, их_колонка2)

FOREIGN KEY и изменение данных

А теперь представь ситуацию, когда в таблице employee мы решили обновить некоторые данные и у нас поменялся id сотрудника. Что при этом произойдет с данными в таблице task? Правильно, они станут неактуальными, и целостность нашей базы данных нарушится.

Чтобы такого не произошло, можно указать SQL-серверу, чтобы он при изменении id в таблице employee поменял бы и employee_id всех строк во всех таблицах, которые ссылаются на это конкретное изменившееся id.

Такие сценарии называются OnUpdate и OnDelete. Что сделать в случае изменения id-записи, и что делать в случае удаления записи?

С удалением вообще не все так однозначно. Если у тебя есть зависимые объекты, представленные строками в БД, которые ссылаются друг на друга, то возможны самые разнообразные сценарии поведения при удалении одного объекта.

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

Или увольняется сотрудник. Было бы странно, чтобы он уволился и одновременно из базы исчезли все задачи, которые на него назначены. Но если бы они остались назначенными не него, тоже вышло бы нехорошо. Правильнее сделать так, чтобы сотрудник мог уволиться после того, как переназначил все свои задачи на других людей.

Вот как мы можем описать эти сценарии с помощью FOREIGN KEY. Общий вид такого ключа/правила имеет вид:

FOREIGN KEY (колонка) REFERENCES таблица(колонка) [ON DELETE reference_option] [ON UPDATE reference_option]

Что делать в случае удаления (ON DELETE) или изменения (ON UPDATE) записей? Всего может быть 5 вариантов действия SQL-сервера в каждой из этих ситуаций:

Вот как бы мы могли изменить нашу таблицу task:

ALTER TABLE task ADD FOREIGN KEY (employee_id) REFERENCES employee(id) ON UPDATE CASCADE ON DELETE RESTRICT;

Что тут написано:

ON UPDATE CASCADE: Если меняется ключ id в таблице employee, то так же поменять employee_id в таблице task, которая на него ссылается.

ON DELETE RESTRICT: Если удаляется строка из таблицы employee и на нее есть ссылка из таблицы task, то запретить удаление строки из таблицы employee.

1

Задача

Модуль 4. Работа с БД, 6 уровень, 7 лекция

Недоступна

Создание таблицы с FOREIGN KEY

Допиши в скрипт создания таблицы добавление FOREIGN KEY по полю user_id с ссылкой на поле id таблицы users. Никаких других изменений в скрипт не вноси.

Открыть

1

Задача

Модуль 4. Работа с БД, 6 уровень, 7 лекция

Недоступна

Добавление в таблицу FOREIGN KEY

Напиши скрипт добавления FOREIGN KEY в таблицу event по полю user_id с ссылкой на поле id таблицы users.

Открыть

1

Задача

Модуль 4. Работа с БД, 6 уровень, 7 лекция

Недоступна

task0631

task0631

Открыть

1

Задача

Модуль 4. Работа с БД, 6 уровень, 7 лекция

Недоступна

Добавление в таблицу FOREIGN KEY с опциями

Напиши скрипт добавления FOREIGN KEY с опциями ON UPDATE CASCADE, ON DELETE SET NULL в таблицу event по полю user_id с ссылкой на поле id таблицы users.

Открыть