Дополнение к циклу переведенных статей.
Статьи: 1-3, 4-6, 7-9, 10-13, 14-15
Информация в статье относится к 5-й части руководства.
В комментариях один из пользователей небеспричинно упрекнул в отсутствии информации о каскадном удалении данных. Восполняю пробел. У автора статей нет информации на эту тему, поэтому я написал небольшую статью об этом. Она достаточно логично впишется в указанный цикл.
Для начала, чтобы не было путаницы, стоит сказать, что речь не столько и не только о каскадном удалении данных, а о теме ссылочной целостности и внешних ключах, частью которой и является каскадное удаление данных.
Введение.
Если отталкиваться от обывательской позиции человека, который разрабатывает базы данных, то внешние ключи – это удобно и упрощает жизнь (в большинстве случаев, всегда есть исключения.). Даже будучи невеждой в реляционной теории баз данных, к осознанной необходимости использования внешних ключей, на определенном этапе своего развития, приходит практически любой практик (утверждение — более относится к начинающим), который не стоит на месте в своем развитии и продолжает мыслить. Даже если он еще не знает, что то, что ему нужно называется связью по внешнему ключу, он начинает самостоятельно организовывать данные определенным образом, разбивать на отдельные таблицы и связывать их между собой. Настолько это становится очевидным.
Но при использовании внешних ключей, даже если не знать такого определения, возникает необходимость следить за связываемыми данными. Рассматриваемым объектом данной статьи является, если так можно сказать, своеобразный спутник, который следует за такой организацией данных. И в данном случае уже гораздо полезнее знать теорию, т.к. это может значительно упростить жизнь в процессе работы с базой данных.
Ближе к сути.
О внешних ключах было рассказано в переводах, останавливаться не буду на этом. Расскажу о “спутнике”.
В случае, если не знать теории, следить за связями данных придется самостоятельно. Альтернативным вариантом является возложение этой задачи на базу данных. Что же за, так названное, слежение за связями данных? Чтобы понять, нужен пример.
У нас есть какие-то вещи. Они разбросаны, их много. Мы хотим навести порядок. Порядок – это, зачастую, классификация (категоризация) и опись. Мы хотим порядка, при этом, мы умеем работать с базами данных и не хотим ничего писать на бумаге. Мы записываем все вещи “в столбик”. Далее мы просматриваем список и определяем категории к которым относятся вещи.
Пусть это часть наших вещей, остальные не рассматриваем:
- книга 1
- книга 2
- книга 3
- компьютерная мышка
- клавиатура
- ручка
- степлер
Определяем для себя, что:
Книга 1, книга 2, книга 3 – это книги, как ни странно.
Компьютерная мышка, клавиатура – это компьютерная периферия.
Ручка, степлер – это канцелярские принадлежности.
Мы создаем две таблицы в базе данных: categories (категории) и stuff(вещи).
Categories (категории)
category_id | name
1 | книги
2 | компьютерная периферия
3 | канцелярские принадлежности
Stuff (вещи)
stuff_id | category_id | name
1 | 1 | книга 1
2 | 1 | книга 2
3 | 1 | книга 3
4 | 2 | компьютерная мышка
5 | 2 | клавиатура
6 | 3 | ручка
7 | 3 | степлер
P.S. Изображения с habrastorage.org/ не отображаются.
Итого: у нас есть книги, компьютерная периферия, канцелярские принадлежности.
Мы захотели выкинуть или подарить все наши книги, не хотим видеть эти вещи, как категорию, у себя дома, нам нравятся электронные книги. Мы удаляем из таблицы категорий категорию “книги”. При этом, у нас остаются вещи из этой категории в другой таблице, мы ссылаемся на эти категории в таблице вещей. Это и называется нарушением ссылочной целостности. Казалось бы, нет у нас категории, а значит и нет книг, но записи в таблице вещей остались и вещей-то у нас много и в будущем положение дел может повториться и повторится и тогда у нас будет бардак, много лишней информации и все вытекающие последствия как в удобстве работы с нашей информацией, так и в технической части при работе с базой (напр., поиск информации). И тут приходит понимание, что нам нужно работать с двумя таблицами, следить в каких случаях связи могут быть нарушены, сломаны и совершать какие-то телодвижения и тут есть два варианта: самостоятельно делаем это или, вот тут знание – сила, мы может переложить эту головную боль на базу данных.
В рамках реляционной модели данных таблица категорий является предком, а таблица вещей – потомком. Тут все понятно, как родитель и ребенок. Более того, случаи, в которых связи могут быть сломаны, также определены (берем и пользуемся). Наш случай – не единственный.
Сломаться связи могут (если говорить “правильным” языком – ссылочная целостность может нарушиться) в следующих случаях:
- обновляется внешний ключ (ссылка на идентификатор в таблице категорий) в строке-потомке. Мы обновляем категорию (цифру, идентификатор этой категории) у какой-то вещи, и ошибаемся, нет такой категории. И… имеем подвисшую в воздухе вещь.
- добавляется новая строка-потомок. Добавляем новую вещь, а она не принадлежит ни одной категории. Кстати говоря, добавить категорию мы можем без вещей. У нас так устроена база данных, что вещь не может быть без категории, а категория может, она ведь не ссылается на вещь.
- удаление строки-предка. Это как раз то, что было в нашем случае. Удалили категорию, а вещи остались.
- обновление первичного ключа в строке-предке. Мы поменяли идентификатор категории, а на прежний идентификатор у нас ссылаются определенные вещи. Итог: часть вещей опять в подвешенном состоянии.
Средства поддержания ссылочной целостности SQL (скажу сразу, наперед, когда будет нужно – поймете; если говорить про РСУБД MySQL, то использование этих средств вместе с внешними ключами возможно только для таблиц InnoDB; внешние ключи можно искользовать в MyISAM, создавая определенную структуру даных, но тогда вся головная боль по слежению за связями ложится на пользователя) позволяют обрабатывать указанные случаи.
И вот как решаются эти проблемы (в порядке перечисления):
- При обновлении в таблице-потомке проверяется новое значение внешнего ключа. Если указываемого значения нет среди первичных ключей таблицы-предка, то возвращается ошибка.
В нашем случае, если мы изменяем для вещи номер категории, а он не существует. - При добавлении новой строки-потомка. Если указываемое значение внешнего ключа не существует среди первичных ключей таблицы-предка, то возвращается ошибка.
В нашем случае, если мы добавляем вещь и указываем для нее номер несуществующей категории.
Теперь два последних. Тут положение дел более интересное.
- Удаление строки предка. В нашем случае, если мы удалим категорию, а на нее будут ссылаться вещи в таблице вещей. Решений проблемы может быть несколько. Какое из них предпринимать решается разработчиком базы данных (вы указываете).
1) при удалении категории можно удалять все относящиеся к ней вещи.
2) запретить удалять категории, пока на них ссылаются какие-то вещи.
3) для вещей, которые ссылаются на удаляемую категорию, указать значение NULL в качестве ссылки на категорию.
4) для вещей, которые ссылаются на удаляемую категорию, указать значение по умолчанию, отличное от NULL. - В случае обновления первичного ключа в строке-предке – аналогичные предыдущим варианты.
Теперь о каскадном удалении данных, о котором говорилось в начале.
При задании правил удаления и(или) обновления в SQL используются определенные синтаксические конструкции:
FOREIGN KEY [key_name] (col1, …) REFERENCES table (table_col, …)
[ON DELETE {CASCADE|SET NULL|NO ACTION|RESTRICT|SET DEFAULT}]
[ON UPDATE {CASCADE|SET NULL|NO ACTION|RESTRICT|SET DEFAULT}]
Где необязательные конструкции ON DELETE и ON UPDATE позволяют задать те самые варианты решения проблемы, которые рассмотрены выше. А эти ключевые слова именуют их:
CASCADE – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, автоматически удаляются или обновляются записи со ссылками на это значение в таблице-потомке. В нашем случае, если мы удалим категорию, то удалятся и все вещи, относящиеся к этой категории в таблице вещей. Если мы обновим идентификатор у категории, то у вещей, которые ссылались на эту категорию, идентификатор также изменится на новый.
То самое, каскадное, но, как видите, не только удаление.
SET NULL – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, значения внешнего ключа в таблице-потомке устанавливаются в NULL.
В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, то у всех вещей, которые ссылались, относились, к данной категории в поле с идентификатором категории будет выставлено NULL.
NO ACTION — при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке никаких действий предприниматься не будет.
В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, то это никак не повлияет на таблицу вещей.
RESTRICT – если в таблице-потомке есть записи, которые ссылаются на существующий первичный ключ в таблице-потомке, то при удалении или обновлении записи с первичным ключом в таблице-предке возвратится ошибка.
В нашем случае, если мы попробуем обновить или изменить идентификатор категории при том, что есть вещи относящиеся к этой категории, то мы получим ошибку.
SET DEFAULT – тут понятно из названия, что при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке соответствующим записям будет выставлено значение по умолчанию. Есть одно “НО”. В РСУБД MySQL это ключевое слово не используется.
А теперь вновь – к каскадному удалению данных. Почему именно оно на слуху? Почему спросили про него в первую очередь, не смотря на то, что оно лишь одно из. Наверное, потому, что каскадное удаление данных наиболее частое решение проблемы.
Автор: hbuser
