Дополнение к серии переведенных статей.
Статьи: 1-3 , 4-6 , 7-9 , 10-13 , 14-15 Информация в этой статье относится к части 5 руководства.
В комментариях один из пользователей справедливо упрекнул в отсутствии информации о каскадном удалении данных.
Я восполняю пробел.
У автора статей нет информации по этой теме, поэтому я написал об этом небольшую статью.
Это вполне логично впишется в указанный цикл.
Для начала, чтобы не было путаницы, стоит сказать, что речь идет не столько и не только о каскадном удалении данных, сколько о теме ссылочной целостности и внешних ключей, частью которых является каскадное удаление данных.
Введение.
Если исходить из обывательской позиции человека, занимающегося разработкой баз данных, то внешние ключи удобны и упрощают жизнь (в большинстве случаев всегда есть исключения.
).
Даже будучи несведущим в реляционной теории баз данных, практически любой практик (утверждение больше относится к новичкам), который не стоит на месте в своем развитии и продолжает думать, на определенном этапе своего развития приходит к осознанной необходимости использования внешних ключей.
Даже если он еще не знает, что то, что ему нужно, называется отношением внешнего ключа, он начинает самостоятельно организовывать данные определенным образом, разбивать их на отдельные таблицы и связывать их между собой.
Это становится настолько очевидным.
Но при использовании внешних ключей, даже если вы не знаете такого определения, возникает необходимость отслеживать связываемые данные.
Рассматриваемый в данной статье объект является, так сказать, своего рода спутником, следящим за такой организацией данных.
И в этом случае гораздо полезнее знать теорию, ведь это может значительно облегчить жизнь при работе с базой данных.
Ближе к делу.
В переводах обсуждались внешние ключи, но я не буду на этом останавливаться.
Расскажу вам о «спутнике».
Если вы не знаете теории, вам придется самостоятельно следить за соединениями данных.
Альтернативный вариант — назначить эту задачу базе данных.
Что такое так называемое отслеживание каналов передачи данных? Чтобы понять, нужен пример.
У нас есть кое-какие вещи.
Они разбросаны, их много.
Мы хотим навести порядок.
Порядок часто представляет собой классификацию (категоризацию) и инвентаризацию.
Мы хотим порядка, но умеем работать с базами данных и не хотим ничего писать на бумаге.
Записываем все дела «столбиком».
Далее просматриваем список и определяем категории, к которым относятся вещи.
Пусть это будет частью наших дел, остальное мы не учитываем: книга 1 книга 2 книга 3 компьютерная мышка клавиатура ручка степлер Мы определяем для себя, что: Книга 1, книга 2, книга 3 – это книги, как ни странно.
Компьютерная мышь, клавиатура Является периферийным устройством компьютера.
Ручка, степлер - Это канцелярские товары.
Создаем две таблицы в базе данных: категории И вещи (вещи) .
Категории идентификатор_категории | имя 1 | книги 2 | компьютерная периферия 3 | канцелярские товары Вещи (вещи) идентификатор_штука | идентификатор_категории | имя 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}]
Где необязательные конструкции? ПРИ УДАЛЕНИИ И ОБНОВЛЕНИЕ позволяют задать те же варианты решения проблемы, которые рассмотрены выше.
И эти ключевые слова называют их: КАСКАД – когда вы удаляете или обновляете запись в родительской таблице, содержащей первичный ключ, записи со ссылками на это значение в дочерней таблице автоматически удаляются или обновляются.
В нашем случае, если мы удалим категорию, то все вещи, относящиеся к этой категории в таблице вещей, будут удалены.
Если мы обновим идентификатор категории, то вещи, которые ссылались на эту категорию, также изменят идентификатор на новый.
То же самое, каскадирование, но, как видите, не только удаление.
УСТАНОВИТЬ НУЛЬ – При удалении или обновлении записи в родительской таблице, содержащей первичный ключ, значения внешнего ключа в дочерней таблице устанавливаются в NULL. В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, то все вещи, на которые ссылались, относятся к этой категории, в поле с идентификатором категории будет NULL. БЕЗДЕЙСТВИЕ — при удалении или обновлении записи в родительской таблице, содержащей первичный ключ, в дочерней таблице не будет предпринято никаких действий.
В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, это никак не повлияет на таблицу вещей.
ОГРАНИЧИВАТЬ – если в дочерней таблице есть записи, ссылающиеся на существующий первичный ключ в дочерней таблице, то удаление или обновление записи с первичным ключом в родительской таблице вернет ошибку.
В нашем случае, если мы попытаемся обновить или изменить идентификатор категории, даже если к этой категории есть вещи, мы получим ошибку.
УСТАНОВИТЬ ПО УМОЛЧАНИЮ – здесь из названия понятно, что при удалении или обновлении записи в родительской таблице, содержащей первичный ключ, соответствующим записям в дочерней таблице будет присвоено значение по умолчанию.
Есть одно «НО».
Это ключевое слово не используется в СУБД MySQL. А теперь снова — каскадное удаление данных.
Почему это так популярно? Почему они вообще об этом спросили, несмотря на то, что это всего лишь один из них.
Вероятно, потому, что каскадное удаление данных является наиболее распространенным решением проблемы.
Теги: #MySQL #sql #проектирование баз данных #MySQL #sql
-
Windows 7 И Ее Ремонт
19 Oct, 24 -
Как Правильно Работать С Коммерческой Тайной
19 Oct, 24
