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


 

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

64111. Редактор растрової графіки AdobePhotoshop 17.47 MB
  Сьогодні безліч професій вимагають певних знань при роботі з графічними редакторами. Це може бути Photoshop, Paint та інші. Такі знання потрібні не тільки дизайнерам і художникам. Найчастіше програмами обробки зображень користуються редактори та веб-майстри...
64112. Расчет когенерации системы теплоснабжения поселка Хону Момского улуса 1.36 MB
  Цель работы: расчет когенерации ДЭС п. Хону Момского улуса планирование оптимальной модернизации и развития системы теплоснабжения; снижение тарифов на отпуск тепловой энергии; увеличения КПД электростанций; улучшение качества теплоснабжения существующих потребителей...
64113. Современные внешние запоминающие устройства 48.36 MB
  Информация на диске записывается в виде спиральной дорожки так называемых питов (углублений), выдавленных на алюминиевом слое (в отличие от технологии записи CD-ROM’ов где информация записывается цилиндрически).
64114. Разработка технологического процесса сборки и сварки сварной конструкции «Каркас безопасности для автомобиля» 435.5 KB
  Автоматизация процессов сварки резко повысила производительность труда и качество сварных соединений. Процесс сварки сопряжен с опасностью возгораний; поражений электрическим током; отравлений вредными газами; поражением глаз и других частей тела тепловым ультрафиолетовым инфракрасным...
64115. Анализа ассортимента и экспертиза качества пива, реализуемого в предприятиях розничной торговли «Тимское» 40.16 MB
  Основной целью выполнения дипломной работы являлось проведение анализа ассортимента и экспертиза качества пива, реализуемого в предприятиях розничной торговли потребительского общества «Тимское».
64116. Организация рабочего места по дефектации деталей цилиндра поршневой группы для лаборатории № 304 5.53 MB
  Автотранспортное предприятие ООО «СЕВЕРО-ЗАПАД АВТОТРАНС» расположено по адресу 6й предпортовый проезд д1 Схема расположения АТП представлена в соответствии с рисунком 1. Административное здание; Ремонтная зона грузовых автомобилей; Прицепной участок...
64117. Структура управления и ее влияние на эффективность работы торгового предприятия (на примере ЗАО «Связной Логистика») 1.74 MB
  Роль структуры управления в эффективном управлении организацией Анализ организационной структуры и структуры управления организацией Анализ управления спросом на товары и услуги предприятия...
64118. Технологія зборки та монтажу плати блоку біжучого рядка на світлодіодах 363.5 KB
  Яскрава картинка яка динамічно змінюється привертає увагу і допомагає донести велику кількість інформації використовуючи мінімум простору. принцип управління схемний; кількість входів 23; Кількість виходів 21; входи з клавіатури та блоку живлення...
64119. Оcновные нaпpaвления деятельноcти оpгaнов пpедвapительного pacледовaния в cовpеменный пеpиод 1.92 MB
  Пpо‏во‏димые‏ в Pо‏ccии cуде‏бно‏ пpaво‏вые‏ pе‏фо‏pмы нaпpaвле‏ны нa эффе‏ктивную paбо‏ту пpaво‏о‏xpaните‏льныx о‏pгaно‏в по‏ бо‏pьбе‏ c пpе‏cтупно‏cтью в cо‏че‏тaнии c гумaнным о‏тно‏ше‏ние‏м к че‏ло‏ве‏ку, по‏пaвше‏му в cфе‏pу уго‏ло‏вно‏- пpо‏це‏ccуaльныx о‏тно‏ше‏ний.