17224

ПОДДЕРЖКА ССЫЛОЧНОЙ ЦЕЛОСТНОСТИ

Лекция

Информатика, кибернетика и программирование

Лекция №3 Поддержка ссылочной целостности Ссылочная целостность может нарушиться в результате операций изменяющих состояние БД. Таких операций три вставка обновление и удаление кортежей в отношениях. Т.к. в определении ссылочной целостности участвуют два отно...

Русский

2013-06-30

69 KB

1 чел.

Лекция №3

Поддержка ссылочной целостности

Ссылочная целостность может нарушиться в результате операций, изменяющих состояние БД. Таких операций три - вставка, обновление и удаление кортежей в отношениях. Т.к. в определении ссылочной целостности участвуют два отношения - базовое и зависимое, а в каждом из них возможны три операции - вставка, обновление, удаление, то возможны шесть различных вариантов.

Для базового отношения

  •  Вставка кортежа. При вставке кортежа в базовое отношение возникает новое значение потенциального ключа. Т.к. допустимо существование кортежей в базовом отношении, на которые нет ссылок из связного отношения, то в данном случае ссылочная целостность не нарушается.
  •  Обновление кортежа. При обновлении кортежа в базовом отношении может измениться значение потенциального ключа. Если есть кортежи в связном отношении, ссылающиеся на обновляемый кортеж, то значения их внешних ключей станут несогласованными. Т.о. обновление может привести к нарушению ссылочной целостности, если это обновление затрагивает значение потенциального ключа.
  •  Удаление кортежа. При удалении кортежа в базовом отношении удаляется значение потенциального ключа. Если есть кортежи в связном отношении, ссылающиеся на удаляемый кортеж, то значения их внешних ключей станут несогласованными. Т.о. Удаление может привести к нарушению ссылочной целостности.

Для связного отношения

  •  Вставка кортежа. Нельзя вставить кортеж в связное отношение, если вставляемое значение внешнего ключа несогласованно. Т.о. вставка может привести к нарушению ссылочной целостности.
  •  Обновление кортежа. При обновлении кортежа в зависимом отношении можно попытаться некорректно изменить значение внешнего ключа. Такое обновление может привести к нарушению ссылочной целостности.
  •  Удаление кортежа. При удалении кортежа в зависимом отношении ссылочная целостность не нарушается.

Таким образом, ссылочная целостность может быть нарушена при выполнении одной из четырех операций:

  •  Обновление кортежа в базовом отношении.
  •  Удаление кортежа в базовом отношении.
  •  Вставка кортежа в зависимое отношение.
  •  Обновление кортежа в зависимом отношении.

Стратегии поддержки ссылочной целостности

Существуют две основные стратегии поддержки ссылочной целостности:

  •  RESTRICT (ОГРАНИЧИТЬ) - не разрешать выполнение операции, приводящей к нарушению ссылочной целостности. Это стратегия требует проверки, имеются ли кортежи в зависимом отношении, связанные с кортежем в базовом отношении.
  •  CASCADE (КАСКАДИРОВАть) - разрешить выполнение требуемой операции, но внести при этом необходимые поправки в других отношениях так, чтобы не допустить нарушения ссылочной целостности и сохранить все имеющиеся связи. Изменение начинается в базовом отношении и каскадно выполняется в связном отношении. В реализации этой стратегии необходимо учитывать следующее: зависимое отношение само может быть базовым для некоторого третьего отношения. При этом может дополнительно потребоваться выполнение какой-либо стратегии и для этой связи и т.д. Если при этом какая-либо из каскадных операций (любого уровня) не может быть выполнена, то не выполняется все операции, начиная от первоначальной, и БД возвращается в исходное состояние. Важность этой стратегии заключается в том, что не нарушается связь между кортежами базового и зависимого отношений.

Эти стратегии являются стандартными и присутствуют во всех СУБД, в которых имеется поддержка ссылочной целостности.

Можно рассмотреть дополнительные стратегии поддержания ссылочной целостности:

  •  SET NULL (УСТАНОВИТЬ В NULL) - разрешить выполнение требуемой операции, но все возникающие несогласованные значения внешних ключей изменять на null-значения. Эта стратегия имеет два недостатка. Во-первых, для нее требуется допустить использование null-значений. Во-вторых, кортежи зависимого отношения теряют всякую связь с кортежами базового отношения. Установить, с каким кортежем базового отношения были связаны измененные кортежи зависимого отношения, после выполнения операции уже нельзя.
  •  SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - разрешить выполнение требуемой операции, но все возникающие несогласованные значения внешних ключей изменять на некоторое значение, принятое по умолчанию. Достоинство этой стратегии по сравнению с предыдущей в том, что она позволяет не пользоваться null-значениями. Недостатки заключаются в следующем. Во-первых, в базовом отношении должен быть некий кортеж, потенциальный ключ которого принят как значение по умолчанию для внешних ключей. В качестве такого "кортежа по умолчанию" обычно принимают специальный кортеж, заполненный нулевыми значениями (не null-значениями!). Этот кортеж нельзя удалять из базового отношения, и в этом кортеже нельзя изменять значение потенциального ключа. Таким образом, не все кортежи базового отношения становятся равнозначными, поэтому приходится прилагать дополнительные средства (программные) для отслеживания этой неравнозначности. Во-вторых, как и в предыдущем случае, кортежи зависимого отношения теряют всякую связь с кортежами базового отношения. Установить, с каким кортежем базового отношения были связаны измененные кортежи зависимого отношения, после выполнения операции уже нельзя.

В некоторых реализация СУБД рассматривается еще одна стратегия поддержания ссылочной целостности:

  •  IGNORE (ИГНОРИРОВАТЬ) - выполнять операции, не обращая внимания на нарушения ссылочной целостности.

Точнее, это не стратегия, а отказ от поддержки ссылочной целостности. В этом случае в зависимом отношении могут появляться несогласованные значения внешних ключей, и вся ответственность за целостность БД ложится на пользователя.

Применение стратегий поддержания ссылочной целостности

Рассмотрим, как применяются стратегии поддержки ссылочной целостности при выполнении операций модификации БД.

При обновлении кортежа в базовом отношении

Допустимые стратегии:

RESTRICT (ОГРАНИЧИТЬ) - не разрешать обновление, если имеется хотя бы один кортеж в связном отношении, ссылающийся на обновляемый кортеж.

CASCADE (КАСКАДИРОВАТЬ) - выполнить обновление и каскадно изменить значения внешних ключей во всех кортежах связного отношения, ссылающихся на обновляемый кортеж.

SET NULL (УСТАНОВИТЬ В NULL) - выполнить обновление и во всех кортежах связного отношения, ссылающихся на обновляемый кортеж, изменить значения внешних ключей на null-значение.

SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - выполнить обновление и во всех кортежах связного отношения, ссылающихся на обновляемый кортеж, изменить значения внешних ключей на некоторое значение, принятое по умолчанию.

IGNORE (ИГНОРИРОВАТЬ) - выполнить обновление, не обращая внимания на нарушения ссылочной целостности.

При удалении кортежа в базовом отношении

Допустимые стратегии:

RESTRICT (ОГРАНИЧИТЬ) - не разрешать удаление, если имеется хотя бы один кортеж в зависимом отношении, ссылающийся на удаляемый кортеж.

CASCADE (КАСКАДИРОВАТЬ) - выполнить удаление и каскадно удалить кортежи в зависимом отношении, ссылающиеся на удаляемый кортеж.

SET NULL (УСТАНОВИТЬ В NULL) - выполнить удаление и во всех кортежах зависимого отношения, ссылающихся на удаляемый кортеж, изменить значения внешних ключей на null-значение.

SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - выполнить удаление и во всех кортежах зависимого отношения, ссылающихся на удаляемый кортеж, изменить значения внешних ключей на некоторое значение, принятое по умолчанию.

IGNORE (ИГНОРИРОВАТЬ) - выполнить удаление, не обращая внимания на нарушения ссылочной целостности.

При вставке кортежа в зависимое отношение

Допустимые стратегии:

RESTRICT (ОГРАНИЧИТЬ) - не разрешать вставку, если внешний ключ во вставляемом кортеже не соответствует ни одному значению потенциального ключа базового отношения.

SET NULL (УСТАНОВИТЬ В NULL) - вставить кортеж, но в качестве значения внешнего ключа занести не предлагаемое пользователем несогласованное значение, а null-значение.

SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - вставить кортеж, но в качестве значения внешнего ключа занести не предлагаемое пользователем несогласованное значение, а некоторое значение, принятое по умолчанию.

IGNORE (ИГНОРИРОВАТЬ) - вставить кортеж, не обращая внимания на нарушения ссылочной целостности.

При обновлении кортежа в зависимом отношении

Допустимые стратегии:

RESTRICT (ОГРАНИЧИТЬ) - не разрешать обновление, если внешний ключ в обновляемом кортеже становится не соответствующим ни одному значению потенциального ключа базового отношения.

SET NULL (УСТАНОВИТЬ В NULL) - обновить кортеж, но в качестве значения внешнего ключа занести не предлагаемое пользователем несогласованное значение, а null-значение.

SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - обновить кортеж, но в качестве значения внешнего ключа занести не предлагаемое пользователем несогласованное значение, а некоторое значение, принятое по умолчанию.

IGNORE (ИГНОРИРОВАТЬ) - обновить кортеж, не обращая внимания на нарушения ссылочной целостности.

В дополнение к приведенным стратегиям пользователь всегда может придумать свою уникальную стратегию поддержки ссылочной целостности.

Языковая спецификация описания данных

  1.  Р. Стивенсон, Р. Плю Освой самостоятельно SQL за 24 часа. Издательство «БИНОМ», 1998. – 400 с.
  2.  Д.Грофф, П.Вайнберг. SQL: полное руководство. - BHV-Киев, 1999.
  3.  Ю. Тихомиров MS SQL Server в подлиннике. – СПб.: БХВ, 2000.
  4.  Л. Шкарина Язык SQL: учебный курс. – СПб.: Питер, 2001.

Для описания реляционных данных используются инструкции языка обработки данных SQL (Structured Query Language - структурированный язык запросов). SQL основывается на некоторой смеси алгебраических и логических конструкций.

Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов, он настолько усложнился, что стал, в основном, инструмент программиста.

В SQL условно определены два подмножества языка:

  •  SQL-DDL (Data Definition Language) - язык определения структур и ограничений целостности баз данных. Сюда относятся команды создания и удаления баз данных; создания, изменения и удаления таблиц; управления пользователями и т.д.
  •  SQL-DML (Data Manipulation Language) - язык манипулирования данными: добавление, изменение, удаление и извлечение данных, управления транзакциями.

Замечание. Следует отметить, что в отличие от "теоретической" терминологии, используемой при описании реляционной модели (отношение, атрибут, кортеж), в литературе при описании SQL часто используется терминология "практическая" (соответственно - таблица, столбец, строка).

Для описания данных используется инструкция SQL CREATE TABLE.

CREATE TABLE <имя таблицы>

   (<имя столбца> <тип(размер)>

   [NOT NULL  UNIQUE],...,

   [PRIMARY KEY <(имя столбца)>],

   [CONSTRAINT <имя ограничения>] [FOREIGN KEY

   <(имя столбца)> REFERENCES <имя базовой таблицы>

   <имя столбца>],...)

Обязательными параметрами инструкции CREATE TABLE являются:

имя таблицы и список столбцов;

для каждого столбца указываются его имя и тип (см. таблицу типов ниже).

Также могут быть указаны параметры:

NOT NULL - в этом случае элементы столбца всегда должны иметь определенное значение;

один из взаимоисключающих параметров:

  •  UNIQUE - значение каждого элемента столбца должно быть уникальным или
  •  PRIMARY KEY - столбец является первичным ключом.

CONSTRAINT <имя ограничения> - определяет имя ограничения на столбец таблицы.

Замечание. Если в БД устанавливается несколько ограничений, то имена ограничений должны быть уникальны.

FOREIGN KEY <(имя столбца)> REFERNECES <имя базовой таблицы> (<имя столбца>) - эта конструкция определяет тот факт, что столбец текущей таблицы является внешним ключом и указывает на первичный ключ таблицы, на которую он ссылается.

Замечание: Инструкция CREATE TABLE реализованная в СУБД Access не поддерживает некоторых предложений, например, таких как DEFEULT, CHEK и др.

Контроль за выполнением указанных условий осуществляет СУБД.

Для удаления таблиц используется инструкция SQL DROP TABLE.

DROP TABLE <имя таблицы> [restrict cascade]

Если используется опция restrict и таблицу ссылается какое-нибудь ограничение (связь), то инструкция DROP TABLE возвратит ошибку. Если же используется опция cascade, то инструкция DROP TABLE выполнится успешно, и вместе с таблицей будут удалены все ограничения (связи).

В Access опции [restrict  cascade] не поддерживаются, и для удаления базовой таблицы необходимо сначала удалить связь инструкцией ALTER TABLE (синтаксис смотри ниже).

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

Добавить столбцы

ALTER TABLE <имя таблицы> ADD COLUMN (<имя столбца> <тип(размер)> [NOT NULL  UNIQUE], [PRIMARY KEY (<имя столбца>)],...)

Удалить столбцы

ALTER TABLE <имя таблицы> DROP COLUMN (<имя столбца>,...)

Модификация типа столбцов

ALTER TABLE <имя таблицы> ALTER COLUMN (<имя столбца> <тип(размер)> [NOT NULL  UNIQUE], [PRIMARY KEY (<имя столбца>)],...)

Определение внешнего ключа. (Установить связь)

ALTER TABLE <имя таблицы> ADD CONSTRAINT <имя ограничения> FOREIGN KEY (<имя поля>) REFERENCES <имя базовой таблицы> (<имя поля>)

Снять ограничения внешнего ключа. (Удалить связь)

ALTER TABLE <имя таблицы> DROP CONSTRAINT <имя ограничения>