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


 

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

82043. Изготовление изделий из бисера 6.73 MB
  В настоящее время бисерная вышивка переживает свой расцвет. Это красиво, модно, современно. Вышивкой из бисера украшают не только платья, кофточки, но и обувь, сумки и многое другое. Основой для вышивки служат холст, лен, бархат, атлас, шерсть. Сукно. Нитки следует брать армированные, чтобы бусинки их не перетирали.
82044. Країни південно-східної Азії у другій половині ХХ-го століття 7.55 MB
  Економічна колонізація Вєтнаму французьким капіталом розпочата у другій половині ХІХ ст. в умовах післявоєнного економічного буму французькі колонізатори вдалися до розширеної експлуатації людських і природних багатств Вєтнаму.
82045. Beethovens Musik ist ewig 85 KB
  Liebe Kunstfreunde, liebe Gäste! Es freut mich euch alle in diesem gemütlichen Saal zu begrüßen. Unsere außerschulische Veranstaltung ist dem genialen deutschen Komponisten Ludwig van Beethoven gewidmet. Schüler 1: Ludvig van Beethoven ist einer der größten Komponisten, Schüler 2: einer der berühmtesten Komponisten der Welt,
82046. Золоті правила країни ввічливості 77 KB
  Серед слів які ми вживаємо є чарівні слова ввічливості. Бережи свій час; тримай кожну річ на своєму місці; дотримуйся свого слова. І посміхаються У відповідь люди Добрі слова ж бо Для кожного любі.Я слова чарівні знаю Вивчила охоче І в розмові їх вживаю Зранку і до ночі Доброго здоровя Галю Добрий день...
82047. «Шануй батька і неньку, то буде тобі скрізь гладенько». Любов до батьків, турбота про них 59.5 KB
  Мета: виховувати у дітей любов та повагу до батьків турботу про них шанобливе ставлення до їхніх настанов та порад бажання щоденно піклуватися про їхнє здоровя не завдавати їм душевного болю негідними вчинками; прищеплювати бажання бути гідними дітьми своїх батьків прагнення приносити щастя в свій дім.
82048. Подорож країною Екологія 84.5 KB
  Мета: Пробудити в школярів особистої відповідальності за охорону навколишнього середовища, виховувати любов до рідного краю. Обладнання: плакати, телевізор, заставки зупинок, малюнки рослин і тварин Червоної книги України, грамзапис мелодій, присвячених природі, модель квітки.
82049. ІВАН МАЛКОВИЧ «ІЗ ЯНГОЛОМ НА ПЛЕЧІ» 33.5 KB
  Наше свято ми сьогодні присвячуємо творчості нашого поета-земляка, людини знаної не тільки в нас на Україні, але й за її межами. Дитячий видавець, директор видавництва «А-Ба-Ба-Га-Ла-Ма-Га», автор шести власних поетичних збірок, член Спілки письменників України – це Іван Малкович.
82050. Світ жінки (розважальне шоу-гра для старшокласників) 70.5 KB
  Мета: поглибити знання учнів про світ жінки його різноманітність; привернути увагу до видатних жінок їх місце у історії та суспільстві; виховувати повагу до жінки та підвищити мотивацію до саморозвитку та самовдосконалення. Коментар: гра створена за сценарієм шоу Я люблю Україну але на тему світу...
82051. Архітектура рідного міста. Стилістичні ознаки будівель-пам’яток архітектури місцевого значення. Вивчення студентами-архітекторами стилістичних ознак будівель для поглиблення фундаментальних фахових знань 77 KB
  Мета: Підвищити рівень знань та розвинути пізнавальні можливості студентівархітекторів при вивченні архітектурних стилів; поглибити знання студентів про стилістичні ознаки будівель навчити розпізнавати памятки архітектури міста Чернівців за стилями.