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 <имя ограничения>


 

А также другие работы, которые могут Вас заинтересовать

40852. Имена собственные во французском языке 71 KB
  Трудные для вас слова лучше выписывать на карточки: с одной стороны — французское слово или словосочетание, с другой — перевод. Вынимая карточки наугад, вы сможете проверить, хорошо ли вы знаете слова. По мере запоминания откладывайте карточки в сторону. Добивайтесь, чтобы у вас не осталось ни одной невыученной карточки.
40853. Носовые гласные во французском языке 145 KB
  Местоимение с глаголом произносятся слитно: nous restons [nurestõ] мы остаемся nous [nu] перед согласной: nous prlons [nuprlõ ] мы говорим [nuz] перед гласной и h: nous pportons [nuzportõ] мы приносим 6....
40854. Множественное число существительных и прилагательных 146.5 KB
  Образуется от формы единственного числа при помощи непроизносимого окончания s: ville s = villes [vil] города grnd s = grnds [grã] большие Существительные и прилагательные которые в единственном числе оканчиваются на s x z не изменяются во множественном числе: succès m успех успехи heureux счастливый счастливые nez m нос носы Во французском языке прилагательные согласуются в роде и числе с существительными: une nouvelle intéressnte интересная новость des nouvelles intéressntes интересные новости 4....
40855. Слитные артикли с предлогами à и de во французском языке 101.5 KB
  Следите за слитным произношением: vous êtes nous vons nous llons vous vez vous llez ils ont. Quelles lngues étrngères prlezvous B. Prlezvous nglis B. Vous llez à l’école.
40857. Употребление неопределенного и определенного артикля 157.5 KB
  Je prends mon petit déjeuner à six heures trente. J’i une journée de trvil très chrgée. НЕОПРЕДЕЛЕННЫЙ АРТИКЛЬ un une des употребляется: а после оборота c'est ce sont: C'est un professeur. Elle est une bonne secrétire.
40858. Недавнее прошедшее время. Passé immédiat 124.5 KB
  ls viennent de commencer ce travail. 2. Je viens de prendre une douche. 3. Nous venons de nous lever. 4. Tu viens de sonner à la porte. 5. Elle vient d’ avoir ce poste. 6. Vous venons de nous préparer à cet examen.
40859. Безличные обороты (il + глагол) во французском языке 103 KB
  C’est clme et gréble. lors ton nouveu logement n’ ps de défuts Notre cuisine n’est ps très clire. En revnche elle est ssez grnde. Trduisez les questions en frnзis et rйpondezy задайте следующие вопросы пофранцузски и ответьте на них: 1.
40860. Текст и упражнения на французском языке 88.5 KB
  Traduisez les questions en franзais et rйpondez-y (задайте следующие вопросы по-французски и ответьте на них): У вас есть дача? Какая она? Вы часто туда ездите? Вы любите ездить за город осенью? Когда вы предпочитаете ездить за город? Почему?