Виктор Гольцман - MySQL 5.0. Библиотека программиста
Рассмотрим на примере, что такое связь между таблицами. Допустим, у нас есть таблицы A и B, и мы хотим их связать. Для этого в каждую строку таблицы A мы должны поместить некую информацию, позволяющую идентифицировать связанную с ней строку таблицы B. Эта информация называется ссылкой, а поля таблицы A, содержащие эту ссылку, – внешними ключами. Наверное, вы уже сами догадались, что в качестве ссылки используется первичный ключ таблицы B, поскольку именно его значения позволят однозначно идентифицировать нужную строку таблицы B. После того как мы во все строки таблицы A поместим ссылки на строки таблицы B, эти таблицы будут связаны. При этом таблица A будет называться дочерней, а таблица B – родительской.
Существует три типа связей, устанавливаемых между таблицами в базе данных.
• Связь «один ко многим».
Этот тип связи используется чаще всего. В этом случае одна или несколько строк таблицы A ссылаются на одну из строк таблицы B.
Для установки связи между таблицами в дочернюю таблицу добавляется внешний ключ (foreign key) – один или несколько столбцов, содержащих значения первичного ключа родительской таблицы (иными словами, во внешнем ключе хранятся ссылки на строки родительской таблицы).
Рассмотрим таблицу, которая содержит сведения о заказах, сделанных клиентами, и является дочерней по отношению к таблице Customers (Клиенты) (табл. 1.2).
Таблица 1.2.
Orders (Заказы)
В таблице Orders внешним ключом является столбец customer_id (клиент), в котором содержатся номера клиентов из таблицы Customers (Клиенты). Таким образом, каждая строка таблицы Orders ссылается на одну из строк таблицы Customers. Например, строка с идентификационным номером 1012 содержит в столбце customer_id (клиент) значение 533: это означает, что заказ № 1012 сделан клиентом ООО «Кускус».
Столбец product_id таблицы Orders также является внешним ключом – он содержит номера товаров из столбца id (идентификатор) таблицы Products (Товары). Таким образом, таблица Orders является дочерней по отношению к таблицам Customers и Products.
• Связь «один к одному».
Такая связь между таблицами означает, что каждой строке одной таблицы соответствует одна строка другой таблицы, и наоборот. Например, если требуется хранить паспортные данные клиентов, можно создать таблицу Passports (Паспорта), связанную отношением «один к одному» с таблицей Customers (Клиенты).
Таблицы, соединенные связью «один к одному», можно объединить в одну. Две таблицы вместо одной используют по соображениям конфиденциальности (например, можно ограничить доступ пользователей к таблице Passports), для удобства (если в единой таблице слишком много столбцов), для экономии дискового пространства (в дополнительную таблицу выносят те столбцы, которые часто бывают пустыми, тогда дополнительная таблица содержит значительно меньше строк, чем основная, и обе они занимают меньше места, чем единая таблица).
Связь «один к одному» может быть организована так же, как связь «один ко многим», – с помощью первичного ключа родительской таблицы и внешнего ключа дочерней. Другой вариант – связь посредством первичных ключей обеих таблиц, при этом связанные строки имеют одинаковое значение первичного ключа.
• Связь «многие ко многим».
Этот тип связи в реляционной базе данных реализуется только с помощью вспомогательной таблицы. Например, если потребуется включить в заказ несколько наименований товаров, связь «многие ко многим» между таблицами Orders (Заказы) и Products (Товары) можно организовать с помощью вспомогательной таблицы Items (Позиции заказа), содержащей столбцы product_ id (номер товара из таблицы Products), qty (количество товаров данного наименования в заказе) и order_id (номер заказа из таблицы Orders). При этом столбцы product_id и qty из таблицы Orders исключаются. Таким образом, таблица Items будет дочерней по отношению к таблицам Orders и Products и каждая строка таблицы Items будет соответствовать одному наименованию товара в заказе.
Как видим, реляционная база данных представляет собой весьма запутанную структуру, в которой все части (то есть записи) ссылаются на другие самым произвольным образом. А раз структура сложная, то неизбежны ее нарушения, происходящие по различным причинам, включая сбои программы, ошибки оператора и др. Последствия такого нарушения могут быть просто катастрофическими: скажем, что будет, если таблица заказов будет неверно ссылаться на таблицу товаров? Вся деятельность фирмы будет дезорганизована – вместо заказанного товара, допустим лопат, заказчику доставят топоры, а то и вовсе ничего, если ссылка на заказанный товар указывает на несуществующую строку таблицы товаров.
Итак, важнейшим понятием теории реляционных баз данных является целостность данных.
Целостность данных
Целостностью данных, хранимых в СУБД, называется их корректность и непротиворечивость.
Базовыми требованиями целостности, которые должны выполняться в любой реляционной базе данных, являются целостность сущностей и целостность связей (ссылочная целостность).
Целостность сущностей означает, что в каждой таблице есть первичный ключ – уникальный идентификатор строки. Первичный ключ не должен содержать повторяющихся и неопределенных значений. Например, если в таблицу Customers (Клиенты) добавить еще одну строку с идентификатором 533 (притом что одна строка с таким идентификатором уже существует в таблице), то целостность сущностей будет нарушена и невозможно будет определить, кому из этих двух клиентов с одинаковыми идентификаторами принадлежат заказы №№ 1012 и 1014.
Целостность связей означает, что внешний ключ в дочерней таблице не содержит значения, отсутствующие в первичном ключе родительской таблицы. Иными словами, строка дочерней таблицы не должна ссылаться на несуществующую строку родительской таблицы. В отличие от первичного, внешний ключ может содержать неопределенные значения (NULL), и в этом случае целостность не нарушится. Например, в таблицу Orders (Заказы) добавлена строка, содержащая в столбце customer_id значение 999. Здесь нарушится целостность связи между таблицами Customers и Orders: с одной стороны, заказ не является «ничьим», так как в этом случае в столбце customer_id было бы установлено значение NULL, с другой стороны, невозможно выяснить имя и адрес клиента, сделавшего этот заказ.
Как видно из приведенных примеров, если целостность данных нарушена, то с ними невозможно нормально работать. Поэтому поддержание целостности данных является одной из основных функций любой СУБД.
Для поддержания целостности сущностей СУБД проверяет корректность значения первичного ключа при добавлении и изменении строк. Механизм поддержания ссылочной целостности более сложный. Помимо проверки корректности значения внешнего ключа при добавлении и изменении строк дочерней таблицы, необходимо также предотвратить нарушение ссылочной целостности при удалении и изменении строк родительской таблицы. Для этого существует несколько способов.
• Запрет (RESTRICT): если на строку родительской таблицы ссылается хотя бы одна строка дочерней таблицы, то удаление родительской строки и изменение значения первичного ключа в такой строке запрещаются. Например, не допускается удаление информации о клиенте из таблицы Customers (Клиенты), если у этого клиента есть зарегистрированные заказы, то есть строки в таблице Orders (Заказы), которые ссылаются на строку со сведениями об этом клиенте.
• Каскадное удаление/обновление (CASCADE): при удалении строки из родительской таблицы автоматически удаляются все ссылающиеся на нее строки дочерней таблицы; при изменении значения первичного ключа в строке родительской таблицы автоматически обновляется значение внешнего ключа в ссылающихся на нее строках дочерней таблицы.
Например, при удалении записи о клиенте из таблицы Customers (Клиенты) автоматически удаляются сведения о заказах этого клиента, то есть соответствующие строки в таблице Orders (Заказы).
• Обнуление (SET NULL): при удалении строки и при изменении значения первичного ключа в строке значение внешнего ключа во всех строках, ссылающихся на данную, автоматически становится неопределенным (NULL). Например, при удалении записи о клиенте из таблицы Customers (Клиенты) заказы этого клиента автоматически становятся «ничьими», то есть в соответствующих строках таблицы Orders (Заказы) в столбце customer_id (клиент) устанавливается значение NULL.
В СУБД MySQL способ поддержания целостности связи указывается при создании или изменении структуры дочерней таблицы.
С понятием целостности данных тесно связано понятие транзакции. Транзакцией называется группа связанных операций, которые должны быть либо все выполнены, либо все отменены. Если при выполнении одной из операций происходит ошибка или сбой, то транзакция отменяется. При этом все уже внесенные другими операциями изменения автоматически аннулируются и восстанавливается исходное состояние базы данных. Важнейшее применение транзакций – это объединение тех операций, которые, будучи выполнены по отдельности, могут нарушить целостность данных. Например, рассмотренная выше операция каскадного удаления выполняется как единая транзакция: строка родительской таблицы должна быть удалена вместе со всеми ссылающимися на нее строками дочерней таблицы, а если по каким-либо причинам одну из этих строк удалить невозможно, то не будет удалена ни одна из строк.
