Правила целостности данных
Главная особенность SQL-технологий наличие у сервера СУБД специальных средств контроля целостности данных, не зависящих от клиентских программ и привязанных непосредственно к таблицам. Т.е. принципиально не важно, каким образом осуществляется доступ к базе данных: через SQL-консоль, через ODBC-драйвера из приложения Windows, через WWW-connector из Internet-браузера или через DBI-интерфейс Perl. В любом из этих случаев, за контролем целостности данных следит сервер, и при нарушении правил целостности данных сервер известит клиента об ошибке.
К структурам контроля целостности данных относятся ограничители (constraint), которые привязаны к столбцам и триггеры (trigger), которые могут быть привязаны как к столбцам, так и к строкам в таблице.
Ограничители это элементарные проверки или условия, которые выполняются для операций вставки и модификации значения столбца. Если данная проверка не проходит или условие не выполняется, то вставка или модификация отменяется, а в программу клиента передается ошибка.
SQL-серверы, как правило, поддерживают следующие ограничители.
NOT NULL — проверка на непустое значение. NULL — специальное понятие в СУБД, которое означает «пусто». «Пусто» и «0(ноль)» не равны друг другу!
UNIQUE — проверка на уникальность. Вставляемое значение должно быть уникально для данного столбца по всей таблице. Может содержать пустые значения.
PRIMARY KEY — первичный ключ. Значение в столбце считается первичным ключом, если оно непустое и уникально в пределах столбца данной таблицы. Первичный ключ может быть составным и представлять собой комбинацию столбцов. Тогда чтобы считаться первичным ключом, каждое из группы значений не должно быть пустыми и формируемые строки значений первичного ключа должны быть уникальны в пределах таблицы. Первичный ключ — основа для построения индексов по таблице.
SQL-технология позволяет на уровне столбца задавать домены значений, т.е. строго определенные наборы или диапазоны значений, для помещаемых в столбец данных. В частности можно реализовывать ограничения ссылочной целостности (referential integrity constraint) и проверки фиксированного условия. Ограничение ссылочной целостности не позволяет значениям из столбца одной таблицы принимать значения кроме как из присутствующих в столбце другой таблицы. Это делается при помощи ограничителей FOREIGN KEY (внешний ключ) и REFERENCES (указатель ссылки). Таблица, содержащая FOREIGN KEY, считается родительской таблицей. Таблица, содержащая REFERENCES, считается дочерней таблицей. Внешний ключ и указатель ссылки могут находиться в одной таблице, т.е. родительская таблица одновременно является дочерней.
FOREIGN KEY — внешний ключ. Назначает столбец или комбинацию столбцов в текущей (родительской) таблице в качестве внешнего ключа для ссылки из других таблиц.
REFERENCES — указатель ссылки (или родительский ключ). Указывает на столбец (комбинацию столбцов) в родительской таблице, ограничивающую значения в текущей (дочерней) таблице.
Для использования ограничений ссылочной целостности должны выполняться некоторые условия. В частности, родительская и дочерняя таблицы должны находиться в пределах одного аппаратного сервера базы данных, они не могут находиться на различных узлах распределенной базы данных. Столбцы, участвующие в отношении ограничения ссылочной целостности обязаны иметь один и тот же тип данных.
Ограничения ссылочной целостности используются при каскадном удалении, т.е. при удалении записи в родительской таблице удаляются все записи с указанным ключом из дочерних таблиц, и наоборот при запрете удаления/модификации, т.е. при наличии зависимых записей в дочерних таблицах, значение ключа записи в родительской таблице нельзя удалить или модифицировать.
CHECK — проверка фиксированного условия. В данном ограничителе явно указывается условие, которое должно выполняться для вставляемого или модифицируемого значения в столбце. Например: check (user in ‘ALEX’,’JUSTAS’) — в столбце user могут содержаться только значения ‘ALEX’ и ‘JUSTAS’, попытка вставки значения ‘SHTIRLITZ’ будет интерпретирована как ошибочная , check (user_salary between 1000 and 5000) — столбец user_salary может принимать целочисленные значения в диапазоне от 1000 до 5000 и т.д. При формировании условий с некоторыми ограничениями могут использоваться функции, например check (user = upper(user)), в данном случае имя пользователя должно вводиться только в верхнем регистре. Есть и ограничения, например, CHECK не может содержать подзапросы (SELECT).
Обычно ограничители задаются при создании таблиц. Но в дальнейшем их можно изменять, удалять или временно запрещать при помощи соответствующих команд СУБД.
Триггеры — это сохраненная откомпилированная процедура, которая связана с определенной таблицей. Триггеры, в отличие от ограничителей, могут выполнять сколь угодно сложные манипуляции над данными. Помимо операций модификации и вставки, триггеры могут срабатывать и при удалении данных из таблицы. Можно также задавать порядок срабатывания триггера относительно операции, т.е. выполниться ли триггер перед операцией вставки/модификации/удаления значения из столбца (или всей строки) или непосредственно после такой операции.
Некоторые типовые применения триггеров:
- Прозрачный аудит (не зависящий от клиентских программ и невидимый для них) и регистрация событий, связанных с доступом к определенным таблицам или столбцам в таблицах.
- Генерация значений в столбцах на основе значений в других столбцах при вставке/модификации строки данных.
- Манипуляции над зависимыми таблицами в особенности, если они находятся на других узлах распределенной базы данных, чего нельзя сделать при помощи ограничителей.
В случае необходимости триггеры можно запрещать, а затем разрешать. Запрещение триггеров применяется обычно при массовых загрузках данных в таблицы извне, с целью уменьшения времени загрузки. Понятие триггера как выполнение кода по событию в том же Oracle используется весьма широко. В частности, оно является основным при разработке клиентских программ при помощи SQL*Forms. Триггеры пишутся на процедурных расширениях SQL.
Обработка данных в многопользовательской СУБД.
Основное требование к многопользовательским СУБД — обеспечение непротиворечивости данных в системе, при сохранении максимальной производительности и конкуренции в доступе к данным для пользователей.
Конкуренция в доступе к данным означает, что каждый из пользователей независим от остальных пользователей в потребности обработки данных. Система, во избежание порчи данных, самостоятельно устанавливает очередность работы с данными для пользователей. В случае необходимости пользователи могут ожидать своей очереди для работы с данными. Одной из главной целей многопользовательской СУБД является максимальное уменьшение этого времени ожидания до такой степени, чтобы оно (в идеале) стало незаметным для пользователя.
Кроме того, сервер СУБД должен предотвращать взаимно разрушающие манипуляции с данными нескольких пользователей при их одновременной работе. Например, если система не предусматривает такую возможность, то менеджеры принимающие заказы от клиентов на поставку товара, и выполняющие их резервирование на складе, могут зарезервировать товара больше чем фактически имеется в наличии. В этом случае обеспечен неприятный разговор с клиентом, заказ которого будет впоследствии отменен.
Более неприятная ситуация возможна в банке: если одновременно исполняется несколько клиентских платежных поручений с одного счета, то при неконтролируемом списании с клиентского счета возможен отрицательный остаток, что недопустимо.
Контроль нужен также в системах резервирования билетов на транспорте, чтобы билет на одно и то же место не был продан разными кассирами разным пассажирам.
Несмотря на различия в реализации, серверы СУБД используют общие способы управления данными и доступом к ним.
Атомарность SQL-выражений при работе с данными.
Под атомарностью выражения понимается неизменность (фиксация во времени) набора данных, с которыми это выражение работает на всем протяжении своего исполнения. Т.е. если мы выполняем оператор UPDATE над определенной таблицей, то состояние таблицы на момент начала выполнения операции фиксируется во времени и не изменяется до конца выполнения оператора. Этот набор данных для текущего выполняемого выражения не может быть изменен другим пользователем или даже другой сессией этого же пользователя, которая пытается выполнить операцию модификации этих же данных в этой же таблице.
Распараллеливание операций.
Типовые операции с таблицей в базе данных состоят из многих однотипных операций, например оператор UPDATE, который модифицирует 5000 строк в таблице, по своей сути состоит из 5000 операций, каждая из которых может быть выполнена независимо. В связи с этим такие операторы очень хорошо распараллеливаются при использовании многопроцессорных систем. Это позволяет выровнять нагрузку в системе между разными процессорами, при том условии что СУБД умеет работать в многопроцессорной конфигурации, и уменьшить время ответа системы.
Обеспечение максимальной производительности.
С целью сокращения времени различных пользователей на манипуляции с данными используется ряд следующих методов. Их работа находится на уровне, скрытом даже от программиста СУБД, но о них стоит упомянуть т.к. они иллюстрируют серьезные различия с xBase-технологией.
Строго говоря, эта информация справедлива лишь в отношении Oracle, но другие СУБД используют подобные принципы.
- Процессы, выполняющие чтение блоков данных, никогда не ожидают процессов, выполняющих запись тех же блоков данных.
- Процессы, выполняющие запись блоков данных, при отсутствии явных блокировок со стороны пользователя, не ожидают процессов, выполняющих чтение тех же блоков данных.
- Процессы, выполняющие запись блоков данных, ожидают другие процессы, выполняющие запись, только в случае если они пытаются выполнить запись данных в одни и те же блоки данных.
Данные приемы позволяют существенно уменьшить время ожидания ответа системы и увеличить ее производительность.
Транзакции
Многопользовательские системы широко используют понятие транзакций. Транзакция — это логическая единица работы, которая состоит из одного или нескольких SQL-выражений. Группа выражений отмеченных как транзакция рассматривается как единое и неделимое целое. В случае если в одном из выражений обработки данных происходит ошибка, то транзакция отменяется целиком. Таким образом, система возвращается в состояние предшествующее началу транзакции, обеспечивая при этом физическую и логическую непротиворечивость данных.
Например, мы пытаемся модифицировать таблицу при помощи оператора UPDATE. В одном из столбцов этим оператором устанавливается недопустимое значение с точки зрения правил целостности для этой таблицы. Срабатывание ограничителя приведет к тому, что сервер СУБД не позволит выполнить такую модификацию и известит нас ошибкой, а механизм контроля транзакций вызывает отмену всего выполняемого выражения и производит откат к предыдущему состоянию таблицы, сохраняя, таким образом, целостность и непротиворечивость данных. В данном примере транзакция работает с одним SQL-выражением. В случае если выражений несколько, то откатывается результат работы всех выражений составляющих единую транзакцию.
Чтобы транзакциями можно было пользоваться, в системе должен быть включен режим регистрации транзакций. После этого система сохраняет информацию о предыдущих состояниях объектов в системе в так называемых журналах транзакций. Журналы транзакций — это специальные файлы, управляемые сервером СУБД, в которых записываются все изменения произошедшие с момента начала транзакций для всех транзакций в системе.
Если происходит явное сохранение изменений в системе (по команде COMMIT) или неявное сохранение изменений (по завершению группы SQL-выражений, формирующих транзакцию или по завершению сеанса пользователя), то все изменения произошедшие с момента начала транзакции вносятся в систему, и информация о данной транзакции удаляется из журнала.
Для облегчения управления системой в режиме регистрации транзакций существует возможность задания так называемых промежуточных точек сохранения. Промежуточная точка сохранения по команде SAVEPOINT явно помечает состояние системы и предоставляет возможность восстановления состояния БД на момент ее сохранения по команде ROLLBACK. В данном случае ROLLBACK откатывает систему к указанной точке. Обычно промежуточных точек сохранения для одного пользователя может быть несколько.
В случае если транзакция по каким-то причинам не может быть завершена, то происходит неявный откат. Его причиной, могут быть, например, ошибка при выполнении одного из SQL-выражений, составляющих транзакцию, или обрыв связи с инициатором транзакции. При этом по информации из журнала восстанавливается предыдущее состояние объектов, которые пыталась модифицировать текущая транзакция, после чего информация о транзакции удаляется из журнала. Откат может быть и явным — по команде ROLLBACK.
Данная схема справедлива для Oracle, где транзакция начинается с выполнением первого оператора, прочие сервера могут работать по-другому. Например в Informix DS, транзакция начинается явно, при помощи команды BEGIN WORK.
В SQL-бочке меда есть своя ложка дегтя. Для всех SQL-серверов использующих журнальный режим регистрации транзакций существует проблема, так называемых «длинных» транзакций. Это транзакции, которые затрагивают очень большой объем данных (сопоставимый с количеством свободного места на дисках) и в этом случае журналы регистрации транзакций могут переполниться. Если их рост ничем неограничен, то они могут израсходовать у ОС всю доступную дисковую память, что не есть хорошо, т.к. операционная система и сервер СУБД в этом случае остаются в непредсказуемом состоянии. Если их рост ограничен, то при переполнении журналов СУБД выдает соответствующую ошибку и операция откатывается. Чтобы избежать таких ситуаций программист должен разделить длинную транзакцию на короткие транзакции.
Блокировки.
Для того чтобы пользователи не искажали взаимно используемые данные, сервер СУБД, при многопользовательской работе, использует механизм блокировок. Блокировки по аналогии с базами данных на основе файлов могут быть как разделяемые, так и исключительные. Блокировки могут устанавливаться как на таблицу целиком, так и на строку в таблице. Аналогично в xBase-технологиях: блокировки могут устанавливаться как на xBase-файл, так и на запись в xBase-файле.
Блокировки связаны с транзакциями. Если выполняется отмена транзакции, то снимаются все связанные с этой транзакцией блокировки.
Многие блокировки выполняются неявно для пользователя, они выставляются, например, операторами UPDATE, INSERT. Существуют явные операторы задания блокировок, например, LOCK TABLE или операторы, имеющие клаузы блокировки, например SELECT : FOR UPDATE. Соответственно есть операторы и для снятия блокировок.
Многие SQL-серверы имеют специальные способы обнаружения и предотвращения взаимных блокировок (deadlocks), которые могут занимать ресурсы СУБД на неопределенное время.
Способы, которыми обеспечиваются блокировки, зависят от реализации сервера, и описываются в его документации. Виды блокировок также зависят от используемого сервера. В Informix существуют, т.н. promotional locks, это означает, что если клиентский процесс не может блокировать в исключительном режиме ресурс, то такая блокировка ставится в очередь и ей предоставляется ресурс после снятия текущей исключительной блокировки другого процесса. Oracle7 так не делает, если он не может установить исключительную блокировку в течение указанного сервером времени, то клиент извещается об ошибке.
Ссылочная целостность
Указанное ограничение целостности касается внешних ключей. Внешний ключ – это поле (или множество полей) одной таблицы, являющееся ключом другой (или той же самой) таблицы. Внешние ключи используются для установления логических связей между таблицами. Связь устанавливается путем присвоения значений внешнего ключаодной таблицы значениям ключа другой.
Между двумя или более таблицами базы данных могут существовать отношения подчиненности, которые определяют, что для каждой записи главной таблицы (называемой еще родительской ) может существовать одна или несколько записей в подчиненной таблице (называемой также дочерней ).
Существует три разновидности связи между таблицами базы данных:
Отношение «один-ко-многим» имеет место, когда одной записи родительской таблицы может соответствовать несколько записей дочерней. Связь «один-ко-многим» иногда называют связью «многие-к-одному». И в том, и в другом случае сущность связи между таблицами остается неизменной.
Связь «один-ко-многим» наиболее распространена для реляционных баз данных. Она позволяет моделировать также иерархические структуры данных.
Отношение «один-к-одному» имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней. Это отношение встречается намного реже, чем отношение «один-ко-многим». Его используют, если не хотят, чтобы таблица БД «распухала» от второстепенной информации. Использование связи «один-к-одному» приводит к тому, что для чтения связанной информации в нескольких таблицах приходится производить несколько операций чтения вместо одной, когда данные хранятся в одной таблице.
Отношение «многие-ко-многим» имеет место в следующих случаях:
одной записи в родительской таблице соответствует более одной записи в дочерней таблице ;
одной записи в дочерней таблице соответствует более одной записи в родительской таблице.
Считается, что всякая связь «многие-ко-многим» может быть заменена на связь «один-ко-многим» (одну или несколько).
Часто связь между таблицами устанавливается по первичному ключу, т.е. значениям внешнего ключа одной таблицы присваиваются значения первичного ключа другой. Однако это не является обязательным – в общем случае связь может устанавливаться и с помощью вторичных ключей. Кроме того, при установлении связей между таблицами не требуется непременная уникальность ключа, обеспечивающего установление связи. Поля внешнего ключа не обязаны иметь те же имена, что и имена ключей, которым они соответствуют. Внешний ключ может ссылаться на свою собственную таблицу – в таком случае внешний ключ называется рекурсивным.
Ссылочная целостность определяет: если в таблице существует внешний ключ, то его значение должно либо соответствовать значению первичного ключа некоторой записи в базовой таблице, либо задаваться определителем NULL.
Существует несколько важных моментов, связанных с внешними ключами. Во-первых, следует проанализировать, допустимо ли использование во внешних ключах пустых значений. В общем случае, если участие дочерней таблицы в связи является обязательным, то рекомендуется запрещать применение пустых значений в соответствующемвнешнем ключе. В то же время, если имеет место частичное участие дочерней таблицы в связи, то помещение пустых значений в поле внешнего ключа должно быть разрешено. Например, если в операции фиксации сделок некоторой торговой фирмы необходимо указать покупателя, то поле КодКлиента должно иметь атрибут NOT NULL. Если допускается продажа или покупка товара без указания клиента, то для поля КодКлиента можно указать атрибут NULL.
Следующая проблема связана с организацией поддержки ссылочной целостности при выполнении операций модификации данных в базе. Здесь возможны следующие ситуации:
Вставка новой строки в дочернюю таблицу. Для обеспечения ссылочной целостности необходимо убедиться, что значение внешнего ключа новой строки дочерней таблицы равно пустому значению либо некоторому конкретному значению, присутствующему в поле первичного ключа одной из строк родительской таблицы.
Удаление строки из дочерней таблицы. Никаких нарушений ссылочной целостности не происходит.
Обновление внешнего ключа в строке дочерней таблицы. Этот случай подобен описанной выше первой ситуации. Для сохранения ссылочной целостности необходимо убедиться, что значение внешнего ключа в обновленной строке дочерней таблицы равно пустому значению либо некоторому конкретному значению, присутствующему в поле первичного ключа одной из строк родительской таблицы.
Вставка строки в родительскую таблицу. Такая вставка не может вызвать нарушения ссылочной целостности. Добавленная строка просто становится родительским объектом, не имеющим дочерних объектов.
Удаление строки из родительской таблицы. Ссылочная целостность окажется нарушенной, если в дочерней таблице будут существовать строки, ссылающиеся на удаленную строку родительской таблицы. В этом случае может использоваться одна из следующих стратегий:
NO ACTION. Удаление строки из родительской таблицы запрещается, если в дочерней таблице существует хотя бы одна ссылающаяся на нее строка.
CASCADE. При удалении строки из родительской таблицы автоматически удаляются все ссылающиеся на нее строки дочерней таблицы. Если любая из удаляемых строкдочерней таблицы выступает в качестве родительской стороны в какой-либо другой связи, то операция удаления применяется ко всем строкам дочерней таблицы этой связи и т.д. Другими словами, удаление строки родительской таблицы автоматически распространяется на любые дочерние таблицы.
SET NULL. При удалении строки из родительской таблицы во всех ссылающихся на нее строках дочернего отношения в поле внешнего ключа, соответствующегопервичному ключу удаленной строки, записывается пустое значение. Следовательно, удаление строк из родительской таблицы вызовет занесение пустого значения в соответствующее поле дочерней таблицы. Эта стратегия может использоваться, только когда в поле внешнего ключа дочерней таблицы разрешается помещать пустые значения.
SET DEFAULT. При удалении строки из родительской таблицы в поле внешнего ключа всех ссылающихся на нее строк дочерней таблицы автоматически помещается значение, указанное для этого поля как значение по умолчанию. Таким образом, удаление строки из родительской таблицы вызывает помещение принимаемого по умолчанию значения в поле внешнего ключа всех строк дочерней таблицы, ссылающихся на удаленную строку. Эта стратегия применима лишь в тех случаях, когда полю внешнего ключа дочерней таблицы назначено некоторое значение, принимаемое по умолчанию.
NO CHECK. При удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности данных не предпринимается.
Обновление первичного ключа в строке родительской таблицы. Если значение первичного ключа некоторой строки родительской таблицы будет обновлено, нарушениессылочной целостности случится при том условии, что в дочернем отношении существуют строки, ссылающиеся на исходное значение первичного ключа. Для сохраненияссылочной целостности может применяться любая из описанных выше стратегий. При использовании стратегии CASCADE обновление значения первичного ключа в строкеродительской таблицы будет отображено в любой строке дочерней таблицы, ссылающейся на данную строку.
Существует и другой вид целостности – смысловая (семантическая) целостность базы данных. Требование смысловой целостности определяет, что данные в базе данных должны изменяться таким образом, чтобы не нарушалась сложившаяся между ними смысловая связь.
Уровень поддержания целостности данных в разных системах существенно варьируется.
Идеология архитектуры клиент-сервер требует переноса максимально возможного числа правил целостности данных на сервер. К преимуществам такого подхода относятся:
гарантия целостности базы данных, поскольку все правила сосредоточены в одном месте (в базе данных);
автоматическое применение определенных на сервере ограничений целостности для любых приложений;
отсутствие различных реализаций ограничений в разных клиентских приложениях, работающих с базой данных;
быстрое срабатывание ограничений, поскольку они реализованы на сервере и, следовательно, нет необходимости посылать данные клиенту, увеличивая при этом сетевой трафик;
доступность внесенных в ограничения на сервере изменений для всех клиентских приложений, работающих с базой данных, и отсутствие необходимости повторного распространения измененных приложений клиентов среди пользователей.
К недостаткам хранения ограничений целостности на сервере можно отнести:
отсутствие у клиентского приложения возможности реагировать на некоторые ошибочные ситуации, возникающие на сервере при реализации тех или иных правил (например, ошибок при выполнении хранимых процедур на сервере);
ограниченность возможностей языка SQL и языка хранимых процедур и триггеров для реализации всех возникающих потребностей определения целостности данных.
На практике в клиентских приложениях реализуют лишь такие правила, которые тяжело или невозможно реализовать с применением средств сервера. Все остальныеограничения целостности данных переносятся на сервер.
Лекция 9: Определение ограничений целостности Дается определение понятий целостности данных в стандарте языка SQL. Рассматриваются вопросы определения декларативной и каскадной ссылочной целостности. Приводятся примеры создания ограничений первичного и внешнего ключа, ограничений на значение и по умолчанию, а также примеры создания и использования правил и умолчаний.
Таблицы с ограничениями в стандарте языка
При создании баз данных большое внимание должно быть уделено средствам поддержания данных в целостном состоянии. Рассмотрим предусмотренные стандартом языка SQL функции, которые предназначены для поддержания целостности данных. Эта поддержка включает средства задания ограничений, они вводятся с целью защиты базы данных от нарушения согласованности сохраняемых в ней данных. К таким типам поддержки целостности данных относятся:
ограничения для доменов полей;
ссылочная целостность ;
требования конкретного предприятия.
Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE.
Ссылочная целостность при помощи FOREIGN KEY в MySQL
Ссылочная целостность—это состояние реляционной базы данных в которой записи не могут ссылаться на несуществующие записи в этой базе данных.
FOREIGN KEY—особый вид ограничения(constraint) MySQL, которое позволяет предотвратить нарушение ссылочной целостности при удалении/изменении информации в таблицах предках. Поддержка FOREIGN KEY поддерживается только для таблиц типа InnoDB
Пример нарушения ссылочной целостности
Пусть существуют две таблицы. Catalogs, являющаяся таблицей-предком, содержащие в себе упоминания о категориях товаров в интернет магазине и таблица products являющаяся таблицей-потомком, со всеми товарами этого магазина
При удалении категории из таблицы catalogs, в таблице products останутся товары которые не привязаны ни к одной из категорий, что может повлечь массу проблем для магазина.
Это явление называется нарушением ссылочной целостности
На ссылочную целостность базы данных как правило оказывают четыре типа изменений:
- Добавление новой записи в таблице-потомке. Например добавление новой товарной позиции в таблицу products. Важно заметить что важную роль играет изменение именно таблицы-потомка, т.к изменение таблицы-предка (catalogs) не приведет к нарушению ссылочной целостности, т.к наличие пустой категории товаров допустимо
- Обновление внешнего ключа в таблице-потомке. Эта ситуация похожа на первую и может произойти при изменении у товара ссылки на несуществующий раздел каталога, например товар с id_catalog равным 50
- Удаление записи из таблицы-предка. Эта ситуация рассмотрена выше.
- Изменение записи в таблице-предке. Эта ситуация отличается от рассмотренной выше тем что категория каталога не удаляется а принимает новый id
Обработка изменений при помощи FOREIGN KEY
Для того что бы контролировать ссылочную целостность в базе данных необходимо что бы таблицы были связаны при помощи конструкции FOREIGN KEY, которая имеет вид:
FOREIGN KEY — используется при создании/изменении таблиц-потомков таблицах. В рамках данной статьи FOREIGN KEY, следует использовать в таблице products. Данная конструкция позволяет задать в таблице-потомке внешний ключ с именем index_name на столбцах таблицы которые перечисляется в круглых скобках. Можно использовать один или несколько столбцов.
Ключевое слово REFERENCES задаёт таблицу-предка tbl_name на которую будет ссылаться внешний ключ. Поля таблицы-предка задаются в круглых скобках, один или несколько.
Необязательные конструкции ON DELETE и ON UPDATE, определяют поведение MySQL при удалении/обновлении записей из таблицы-предка.
Допустимые параметры для ключевых слов ON DELETE и ON UPDATE:
- RESTRICT — Если в таблице-потомке существуют записи ссылающиеся на первичный ключ таблицы-предка то при удалении или обновлении записей с этим первичным ключом в таблице предке, будет возвращена ошибка. Ошибка будет возвращаться до тех пор пока не останется ни одной ссылки в таблице потомке. В MySQL данный параметр означает то же самое что и NO ACTION
- CASCADE — При удалении/обновлении записей в таблице-предке, будут так же обновлены/удалены записи из таблицы-потомка с существующим первичным ключом
- SET NULL — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом будут обновлены на NULL
- NO ACTION — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут. В MySQL данный параметр означает то же самое что и RESTRICT
- SET DEFAULT — Это действие зарезервировано но не обрабатывается в InnoDB
Добавление для таблицы products из примера статьи конструкции:
приведет к тому что изменения таблицы catalogs приведет к автоматическому изменению таблицы products.
Проверку ограничения внешнего ключа можно отключить присвоив системной переменной FOREIGN_KEY_CHECKS значение 0
Русские Блоги
MySQL обучения (восемь) ограничения целостности данных
каталог
Первичный ключ — это набор из одного или нескольких столбцов в таблице, значение которых однозначно идентифицирует каждую строку в таблице. Первичный ключ создается путем определения ограничения PRIMARY KEY, а столбцы в ограничении PRIMARY KEY не могут принимать нулевые значения. Поскольку ограничение PRIMARY KEY может обеспечить уникальность данных, оно часто используется для определения столбца флага. Когда для таблицы определено ограничение PRIMARY KEY, MySQL создает уникальный индекс для столбца первичного ключа для достижения уникальности данных. Когда первичный ключ используется в запросе, индекс можно использовать для быстрого доступа к данным. Если ограничение PRIMARY KEY определяется комбинацией нескольких столбцов, значение столбца может дублироваться, но объединенные значения всех столбцов в определении ограничения PRIMARY KEY должны быть уникальными.
Существует два способа определения первичного ключа: как ограничение целостности столбца или таблицы. В качестве ограничения целостности столбца просто добавьте ключевое слово PRIMARY KEY в определение столбца. В качестве ограничения целостности таблицы необходимо добавить оператор PRIMARY KEY (name, . ) в конце оператора.
1. Основные ограничения ключа
В принципе, любой столбец или комбинация столбцов может служить первичным ключом. Но столбец первичного ключа должен следовать некоторым правилам. Эти правила основаны на теории реляционных моделей и правилах, разработанных MySQL:
(1) Только один первичный ключ может быть определен для каждой таблицы.
(2) Теория реляционной модели требует, чтобы первичный ключ был определен для каждой таблицы. Однако MySQL не требует этого для создания таблицы без первичного ключа. Однако с точки зрения безопасности первичный ключ должен быть указан для каждой базовой таблицы.
(3) Две разные строки в таблице не могут иметь одинаковое значение в первичном ключе. Это правило уникальности.
(4) Если столбец удален из составного первичного ключа, остальные столбцы, составляющие первичный ключ, все еще удовлетворяют принципу уникальности, то составной первичный ключ является неправильным. Это правило называется правилом минимальности. То есть составной первичный ключ не должен содержать ненужного столбца.
(5) Имя столбца может появиться только один раз в списке первичных ключей.
MySQL автоматически создает индекс для первичного ключа. Обычно этот индекс называется ПЕРВИЧНЫМ. Однако вы можете переименовать этот индекс.
[Пример 1] Создайте таблицу book_copy и определите имя книги в качестве первичного ключа.
CREATE TABLE book_copy
Книжный номер varchar (6) NULL,
Название книги varchar (20) NOT NULL NULL ПЕРВИЧНЫЙ КЛЮЧ,
Дата публикации Дата
Когда первичный ключ в таблице является составным первичным ключом, его можно определить только как ограничение целостности таблицы.
[Пример 2] Создайте таблицу курсов для записи идентификатора студента, имени, номера курса, кредитов и даты окончания каждого курса. Номер студента, номер курса и дата окончания обучения составляют составной первичный ключ.
CREATE TABLE course
Студенческий номер varchar (6) NOT NULL,
Имя varchar (8) НЕ NULL,
Дата окончания выпускного дня НЕ NULL,
Курс № varchar (3),
ПЕРВИЧНЫЙ КЛЮЧ (номер студента, номер курса, дата окончания)
[Пример 3] Создайте таблицу курсов в Примере 2 и назовите индекс, созданный первичным ключом INDEX_course.
CREATE TABLE course
Студенческий номер varchar (6) NOT NULL,
Имя varchar (8) НЕ NULL,
Дата окончания дата, время, НЕТ,
Курс № varchar (3),
PRIMARY KEY INDEX_course (номер студента, номер курса, дата окончания)
2. Альтернативные ключевые ограничения
В реляционной модели замещающий ключ, например первичный ключ, представляет собой столбец или набор столбцов таблицы, значения которых всегда являются уникальными. Альтернативные ключи — это ключи-кандидаты, которые не были выбраны в качестве первичных ключей. Ключевое слово, определяющее альтернативный ключ, УНИКАЛЬНО.
[Пример] В таблице book_copy1 номер книги используется в качестве первичного ключа, а столбец с именем книги определяется как альтернативный ключ.
CREATE TABLE book_copy1
Номер книги varchar (20) НЕ NULL,
Название книги varchar (20) НЕ NULL УНИКАЛЬНО,
Дата публикации NULL,
ПЕРВИЧНЫЙ КЛЮЧ (номер книги)
Основные различия между альтернативным и первичным ключами в MySQL заключаются в следующем.
(1) Для таблицы данных может быть создан только один первичный ключ. Но таблица может иметь несколько уникальных ключей, и они могут даже перекрываться. Например, замещающий ключ определен в столбцах C1 и C2, а другой замещающий ключ определен в C2 и C3. Эти два замещающих ключа находятся в C2. Столбцы совпадают, и MySQL это позволяет.
(2) Значение поля первичного ключа не может быть NULL, а значение поля UNIQUE может быть NULL, но должно быть объявлено с NULL или NOT NULL.
(3) При создании ограничения PRIMARY KEY система автоматически сгенерирует индекс PRIMARY KEY. При создании ограничения UNIQUE система автоматически генерирует индекс UNIQUE.
3. Ссылочная целостность
4, ограничения ссылочной целостности
В базе данных Книжного магазина есть много правил, связанных с отношениями между таблицами. Например, только книги в таблице каталога книг могут быть проданы, поэтому все книги в таблице Sell (обозначенные номером книги) должны быть книгами в таблице Book, то есть все номера книг, хранящиеся в таблице Sell Должен существовать в столбце «Номер книги» таблицы «Книга». Все идентификационные номера в таблице продажи также должны отображаться в столбце идентификационных номеров таблицы Members. Этот тип отношений является ограничением ссылочной целостности. Ограничения ссылочной целостности — это особый тип ограничения целостности, реализованный как внешний ключ. Таким образом, столбец номера книги и номер идентификатора в таблице Sell можно определить как внешний ключ. Вы можете определить объявление внешнего ключа при создании или изменении таблицы.
Синтаксис reference_definition для определения внешних ключей выглядит следующим образом:
ССЫЛКИ Имя таблицы [(имя столбца [(длина)] [ASC | DESC], . )]
Обратитесь к описанию ограничения целостности
RESTRICT : Если вы хотите удалить или обновить значение, которое отображается во внешнем ключе столбца, на который есть ссылки, в родительской таблице, отклоните операцию удаления или обновления в родительской таблице. Если вы хотите удалить запись 081102 в таблице xs, поскольку в cj есть записи 081102, операция удаления в таблице xs будет отклонена.
CASCADE : Автоматическое удаление или обновление соответствующих строк в дочерней таблице при удалении или обновлении строк из родительской таблицы. Например, когда номер студента 081102 равен 801102, он обновляется из таблицы xs, вторичный номер таблицы cj равен 081102, а номер 801102 автоматически обновляется.
SET NULL : При удалении или обновлении строки из родительской таблицы соответствующему столбцу внешнего ключа в дочерней таблице присваивается значение NULL. Когда строка 081103 удаляется из таблицы xs, запись 081103 в таблице cj устанавливается в NULL.
NO ACTION : NO ACTION означает, что никакие действия не предпринимаются, то есть, если в ссылочной таблице есть соответствующее значение внешнего ключа, попытки удалить или обновить значение первичного ключа в родительской таблице не допускаются, как в случае RESTRICT.
SET DEFAULT : Эффект такой же, как SET NULL, за исключением того, что SET DEFAULT указывает столбец внешнего ключа в дочерней таблице в качестве значения по умолчанию.
Если действие не указано, оба ссылочных действия по умолчанию будут использовать RESTRICT.
Примечание:
Внешние ключи могут использоваться только в таблицах, созданных с помощью механизма хранения InnoDB.Для других типов таблиц сервер MySQL может анализировать синтаксис FOREIGN KEY в операторе CREATE TABLE, но не может использовать или сохранять его.
Измените механизм хранения таблицы на InnoDB, синтаксис следующий:
Alter table Имя таблицы ENGINE = InnoDB
[Пример 1] Создайте таблицу book_ref. Все номера книг в таблице book_ref должны появиться в таблице Book. Предполагается, что столбец номера book использовался в качестве первичного ключа таблицы Book.
CREATE TABLE book_ref
(Номер книги varchar (20) NULL,
varchar (20) НЕ NULL,
Дата публикации NULL,
ОСНОВНОЙ КЛЮЧ (название),
ИНОСТРАННЫЙ КЛЮЧ (номер книги)
ЛИТЕРАТУРА Книга (номер книги)
ON DELETE RESTRICT
ON UPDATE RESTRICT
[Пример 2] Создание таблицы book_ref1 со ссылочным действием CASCADE.
CREATE TABLE book_ref1
Книжный номер varchar (20) NULL,
Название книги varchar (20) NOT NULL,
Дата публикации NULL,
ОСНОВНОЙ КЛЮЧ (название),
ИНОСТРАННЫЙ КЛЮЧ (номер книги)
ЛИТЕРАТУРА Книга (номер книги)
ON UPDATE CASCADE
Эффект этого ссылочного действия заключается в том, что при обновлении основной таблицы дочерняя таблица генерирует связанное действие обновления. Некоторые люди называют это " каскадный «Операция. То есть, если значение номера книги« ISBN7-115-12683-6 »в таблице книг изменяется на« ISBN7-115-12683-1 », столбец номера книги в таблице book_ref1 будет« ISBN7 » -115-12683-6 "был изменен на" ISBN7-115-12683-1 "
5. Проверьте ограничения
Первичные ключи, суррогатные ключи и внешние ключи являются примерами общих ограничений целостности. Однако каждая база данных также имеет определенные ограничения целостности. Например, номер недели в таблице KC должен быть от 1 до 7, а дата рождения в таблице XS должна быть больше 1 января 1986 года. Такие правила могут быть определены с использованием ограничений целостности CHECK.
CHECK Ограничения целостности определяются при создании таблицы. Может быть определено как ограничение целостности столбца или ограничение целостности таблицы.
Объяснение: expr — это выражение, которое определяет условия, которые необходимо проверить.При обновлении данных таблицы MySQL проверяет, соответствуют ли обновленные строки данных условию CHECK.
Однако, к сожалению, в текущей версии MySQL ограничения целостности CHECK не были применены. Ограничения CHECK, определенные в приведенном выше примере, будут проанализированы MySQL, но будут игнорироваться, то есть Ограничение CHECK здесь — это всего лишь комментарий , Не будет играть никакой роли. Я верю, что это может быть расширено в будущих версиях.
[Пример 1] Создайте таблицу ученика, учитывая только два столбца числа ученика и пола.Гендер может включать только мужчину или женщину.
CREATE TABLE student
Char (2) NOT NULL
Здесь ограничение целостности CHECK определяет, какое значение допустимо для пола, потому что CHECK Включено в определение самого столбца, поэтому CHECK Ограничения целостности определяются как ограничения целостности столбца.
[Пример 2] Создайте таблицу student1, используя только два столбца: номер студента и дату рождения. Дата рождения должна быть больше 1 января 1980 года.
CREATE TABLE student1
Дата рождения NOT NOT NULL
ПРОВЕРКА (дата рождения> ‘1980-01-01’)
6, удалить ограничения целостности
Если вы используете один DROP TABLE Заявление об удалении таблицы, все ограничения целостности автоматически удаляются. Все внешние ключи ссылочной таблицы также удаляются.С помощью оператора ALTER TABLE целостность может быть удалена независимо без удаления самой таблицы. Синтаксис для удаления такой же, как и для удаления индекса.