Tw-city.info

IT Новости
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ограничение диапазона значений домена относится к

Вопрос 9. Обеспечение целостности базы данных. Ограничения целостности, относящиеся к атрибутам, записям, таблицам, связям между таблицами. Виды ограничений. Привести примеры.

Вопрос 9. Обеспечение целостности базы данных. Ограничения целостности, относящиеся к атрибутам, записям, таблицам, связям между таблицами. Виды ограничений. Привести примеры.

Целостность данных обеспечивается набором специальных предложений, называемых ограничениями целостности.

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

Ограничения целостности могут относиться к разным информационным объектам: атрибутам, кортежам, отношениям, связям между ними и тому подобное.

Для полей (атрибутов) используются следующие виды ограничений:

• Тип и формат поля .

• Задание диапазона значений.

• Недопустимость пустого поля.

• Проверка на уникальность значения какого-либо поля. Ограничение позволяет избежать записей-дубликатов.

Ограничения, используемые только при проверке допустимости корректировки, называют ограничениями перехода .

Ограничения целостности, относящиеся к кортежам: здесь имеется в виду либо ограничение на значение всей строки, рассматриваемой как единое целое (естественным ограничением является требование уникальности каждой строки таблицы), либо ограничения на соотношения значений отдельных полей в пределах одной строки (например, значение поля «стаж» не должно превышать «возраст»).

Ограничения, проверяющие соотношения между записями одной таблицы, например, «год рождения матери» должен быть меньше, чем «год рождения ребенка»; нельзя быть родителем и ребенком одного и того же человека.

В качестве примера ограничений, относящихся ко всей таблице можно привести следующий. Предположим, что фонд заработной платы формируется исходя из величины средней заработной платы одного сотрудника, которая составляет 10 000 р. Тогда в качестве ограничения целостности таблицы может быть задано выражение, указывающее, что среднее значение поля «оклад» должно быть не больше 10 000.

Имеются ограничения, относящиеся к нескольким взаимосвязанным таблицам, например, ограничение целостности связи , которое выражается в том, что значение атрибута, отражающего связь между объектами и являющегося внешним ключом отношения, обязательно должно совпадать с одним из значений атрибута, являющегося ключом отношения, описывающего соответствующий объект. Например, в БД имеются три таблицы: «Преподаватели», «Дисциплины» и таблица, отражающая связь между преподавателями и дисциплинами: код преподавателя в последней из трех таблиц должен соответствовать одному их кодов в таблице «Преподаватели», а код дисциплины – значению соответствующего поля в таблице «Дисциплины».

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

Ограничения целостности разделяют по моменту контроля за соблюдением ограничения – на одномоментные и отложенные. Отложенные ограничения целостности могут не соблюдаться в процессе выполнения какой-либо группы операций, но обязаны быть соблюдены по завершению выполнения этой группы операций.

Если какая-либо из операций транзакции не выполнена, то все выполненные ранее операции отменяются, и данные возвращаются к тому состоянию, которое они имели до начала выполнения транзакции. Примером может служить перевод денег с одного банковского счета на другой, состоящий из двух операций: удаление денег с одного счета и добавление такой же суммы денег на другой счет.

Ограничения целостности разделяют по способу задания – на явные и неявные. Неявные ограничения определяются спецификой модели данных и проверяются СУБД автоматически. Неявные ограничения обычно относятся к классу синтаксических ограничений в отличие от семантических ограничений целостности, обусловленных спецификой предметной области.

Понятие же целостности может относиться и к служебной информации.

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

Определение ограничений целостности

Ограничение уникального ключа (UNIQUE)

Это ограничение задает требование уникальности значения поля (столбца) или группы полей (столбцов), входящих в уникальный ключ , по отношению к другим записям. Ограничение UNIQUE для столбца таблицы похоже на первичный ключ : для каждой строки данных в нем должны содержаться уникальные значения. Установив для некоторого столбца ограничение первичного ключа , можно одновременно установить для другого столбца ограничение UNIQUE . Отличие в ограничении первичного и уникального ключа заключается в том, что первичный ключ служит как для упорядочения данных в таблице, так и для соединения связанных между собой таблиц. Кроме того, при использовании ограничения UNIQUE допускается существование значения NULL , но лишь единственный раз.

Ограничение на значение (NOT NULL)

Для каждого столбца таблицы можно установить ограничение NOT NULL , запрещающее ввод в этот столбец нулевого значения.

Ограничение проверочное (CHECK) и правила

Данное ограничение используется для проверки допустимости данных, вводимых в конкретный столбец таблицы, т.е. ограничение CHECK обеспечивает еще один уровень защиты данных.

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

Допускается применение нескольких ограничений CHECK к одному и тому же столбцу. В этом случае они будут применимы в той последовательности, в которой происходило их создание. Возможно применение одного и того же ограничения к разным столбцам и использование в логических выражениях значений других столбцов. Указание параметра NOT FOR REPLICATION предписывает не выполнять проверочных действий, если они выполняются подсистемой репликации.

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

Правило может быть создано командой:

Чтобы связать правило с тем или иным столбцом какой-либо таблицы, необходимо использовать системную хранимую процедуру:

Чтобы отменить правила , следует выполнить следующую процедуру:

Удаление правила производится командой

Ограничение по умолчанию (DEFAULT)

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

Отдельно необходимо отметить пользу от использования значений по умолчанию при добавлении нового столбца в таблицу. Если для добавляемого столбца не разрешено хранение значений NULL и не определено значение по умолчанию, то операция добавления столбца закончится неудачей.

При определении в таблице столбца с параметром ROWGUIDCOL сервер автоматически определяет для него значение по умолчанию в виде функции NEWID() . Таким образом, для каждой новой строки будет автоматически генерироваться глобальный уникальный идентификатор.

Дополнительным механизмом использования значений по умолчанию являются объекты базы данных, созданные командой:

Умолчание связывается с тем или иным столбцом какой-либо таблицы с помощью процедуры:

Целостность данных. Ограничения целостности.

« A table or view can have only one unique key .»
Oracle9 i SQL Reference об ограничении использования Ограничений уникальности

Утверждение, представленное в качестве эпиграфа, взято из документации Oracle, но вся практика до прочтения документации указывала на противоположное. Проверка путём создания пары Unique Constraint -ов подтвердила это. Налицо ошибка в документации.

А что ещё (с надеждой на безошибочность описания) можно почерпнуть из документации об Ограничениях целостности в Oracle? Я постарался выписать различные терминологические и функциональные особенности Ограничений целостности как отдельных типов объектов БД Oracle без углубления в синтаксис и подробности их использования. Многое для меня оказалось новым, не буду скрывать.

Начнём с самого начала – Oracle9i Database Concepts Release 2 (9.2). В документации выделяется понятие «Целостность данных» ( Data Integrity ), которое связывается с выполнением бизнес-правил, сопряжённых с БД. Data Integrity делится на пять типов правил, часть из которых обеспечивается «Ограничениями целостности» ( Integrity Constraints ) СУБД Oracle :

1. NULL -правило – NOT NULL ограничение;

2. уникальные значения – ограничения уникального ключа;

3. значения первичного ключа – ограничения первичного ключа;

4. правила ссылочной целостности – ограничения внешнего ключа (или «ограничения ссылочной целостности» – в документации Oracle встречаются оба названия);

5. проверка комплексного ограничения – Check -ограничения.

(Здесь слева от тире представлено правило «Целостности данных», а справа – тип «Ограничений целостности», реализующий это правило)

Четвёртый тип правил «Целостности данных» является составным, и обеспечивается «Ограничениями целостности» лишь частично:

1. выставление в NULL зависимых данных при удалении справочных данных;

2. каскадное удаление зависимых данных при удалении справочных данных;

Читать еще:  Уровень домена active directory

3. а также отсутствие какого либо действия над зависимыми данными при изменении или удалении справочных данных. (Здесь для меня осталась неясность в плане отличия Restrict от No Action . Может, кто из читателей поможет обнаружить различие…)

Оставшиеся существующие подтипы четвёртого пункта «Целостности данных»:

o выставление в NULL зависимых данных при изменении справочных данных;

o каскадное изменении зависимых данных при изменении справочных данных;

o выставление в значение по умолчанию зависимых данных при изменении или удалении справочных данных;

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

Далее для краткости и в силу привычки буду использовать названия «Ограничений целостности» в английском варианте (соотнесение с вышеупомянутыми русскими названиями, на мой взгляд, очевидно), а вместо «Ограничения целостности» писать просто Ограничения.

Итак, UNIQUE Key Constraint . Это Ограничение требует, чтобы каждое значение в поле ключа было уникальным. Под понятием «значение» здесь подразумевается определённая величина, а NULL-значение под данное определение не подпадает, так что одно, два, да даже все поля в ключе UNIQUE Key Constraint могут быть равны NULL. В отличие от ключа PRIMARY Key Constraint , в котором NULL-значение не допускается вовсе.

При создании UNIQUE Key Constraints или PRIMARY Key Constraints неявно создаётся уникальный индекс по тем полям таблицы, на которые накладывается данное Ограничение. Однако, если некий (неважно – уникальный или неуникальный) индекс по полям ключа уже используется, то будет использоваться именно он вместо неявного создания нового. При удалении этих Ограничений будут удаляться и индексы. Уникальные Ограничения, созданные с атрибутом DEFERRABLE (см. ниже) всегда используют неуникальные индексы. При удалении таких Ограничений неуникальные индексы остаются.

Referential Integrity Constraint требует существования в родительской ( справочной ) таблице UNIQUE Key Constraint или PRIMARY Key Constraint. При отсутствии Ограничения NOT NULL на каком либо поле, входящем в Referential Integrity Constraint , в этом поле

допускается NULL -значение, и такой Referential Integrity Constraint будет считаться правильным.

  • Если на внешнем ключе отсутствует индекс. Тогда при удалении или изменении первичного ключа родительской таблицы, Oracle будет выставлять блокировку дочерней таблицы на уровне таблицы, освобождая эту блокировку сразу после её получения. Если внешний ключ определён как ON DELETE CASCADE , то удаление записей из родительской таблицы будет приводить к share-subexclusive блокировкам на дочерней таблице. Разделяемая блокировка всей дочерней таблицы также потребуется при изменении тех полей в родительской таблице, на которые ссылаются поля дочерней таблицы. Разделяемая блокировка позволяет только чтение данных, так что ни вставка, ни удаление, ни изменение данных в дочерней таблице не будут доступны до тех пор, пока не завершится транзакция на родительской таблице.
  • Если на внешнем ключе присутствует индекс, то никаких блокировок на уровне таблицы уже не будет, и при любом удалении или изменении данных в родительской таблице, в дочерней таблице будут блокированы до завершения транзакции только отдельные соответствующие записи (эксклюзивная блокировка на уровне строк).

CHECK Integrity Constraints . Допускаются на одном или нескольких полях таблицы и требует в качестве результата выполнения определённого условия TRUE или UNKNOWN для каждой строки таблицы. Примечательно, что под UNKNOWN подразумевается… NULL! Иными словами, если везде (во всяком случае, следуя той же документации Oracle) NULL -значение не равно ничему, в том числе и самому себе, то здесь оно «работает» как TRUE . Забавно, не так ли?

  • может использоваться только Булево выражение;
  • нельзя использовать подзапросы, SQL-функции или последовательности (интересно, почему?);
  • нельзя использовать SYSDATE , UID , USE R, USERENV , LEVEL , ROWNUM .

Количество CHECK Integrity Constraints неограниченно, но порядок их срабатывания непредсказуем. Ну, и при использовании строчных литералов или таких SQL -функций, как TO_CHAR, TO_DATE, TO_NUMBER с параметрами поддержки глобализации в качестве аргументов, Oracle использует значения этих параметров по умолчанию на уровне базы. Эти значения можно переписать в создаваемом CHECK Integrity Constraint .

Все перечисленные Ограничения, реализованные в Ora c le, допускают их нарушение на уровне оператора, то есть сначала оператор будет полностью выполнен (пускай он коснётся хоть миллиона строк), а потом начнётся проверка Ограничений. Хотя, возможна отложенная проверка Ограничений– до завершения транзакции (о чём речь далее).

Режим SET CONSTRAINTS.

Оператор SET CONSTRAINTS делает Ограничения или DEFERRED , или IMMEDIATE ( DEFERRED и IMMEDIATE относятся к атрибутам Ограничений, о чём речь далее) для части транзакции. Данный оператор можно использовать для установки режима либо для списка Ограничений, либо для всех ( ALL ) Ограничений. Действие данного оператора заканчивается вместе с завершением текущей транзакции, либо с началом действия ещё одного такого же оператора. Данный оператор недоступен в триггерах.

SET CONSTRAINTS … IMMEDIATE вначале вызывает проверку наличия отложенных ранее срабатываний Ограничений, а потом уже срабатывают Ограничения, вызванные выполняющимися операторами в текущей транзакции. Любое нарушение Ограничения при таком процессе будет просигнализировано ошибкой, а при достижении COMMIT’а будет вызван полный откат текущей транзакции. Оператор ALTER SESSION также может иметь выражение SET CONSTRAINTS , но только для всех Ограничений (нельзя их перечислить списком). Это эквивалентно выполнению оператора SET CONSTRAINTS в самом начале каждой транзакции.

Выполнение оператора SET CONSTRAINTS … IMMEDIATE перед самым завершением транзакции позволяет определить успешность предстоящего COMMIT’а и избежать лишних откатов.

С помощью операторов CREATE TABL E или ALTER TABLE можно задавать состояние каждого Ограничения на уровне таблицы, используя следующие выражения:

  • ENABLE гарантирует удовлетворение всех входных данных Ограничению;
  • DISABLE позволяет входным данным не соответствовать Ограничению;
  • VALIDATE гарантирует, что все уже имеющиеся в таблице данные соответствуют Ограничению;
  • NOVALIDATE позволяет уже имеющимся в таблице данным не соответствовать Ограничению;

…и их комбинации:

  • ENABLE VALIDATE аналогично ENABLE и гарантирует, что абсолютно все (и уже вставленные, и вставляемые) записи удовлетворяют Ограничению;
  • ENABLE NO VALIDATE гарантирует удовлетворение Ограничению всех входных данных, однако уже имеющиеся в таблице данные могут не соответствовать Ограничению;
  • DISABLE NOVALIDATE аналогично DISABLE . Не гарантируется удовлетворение Ограничению как входных данных, так и уже имеющихся в таблице;
  • DISABLE VALIDATE отключает Ограничение, удаляет индекс, на котором оно строилось, и запрещает любые изменения на полях, входящих в Ограничение.

… и немного об особенностях применения:

· выражение ENABLE подразумевает ENABLE VALIDATE ;

· выражение DISABLE подразумевает DISABLE NOVALIDATE ;

· VALIDATE и NOVALIDATE ничего не подразумевают в отношении ENABLE и DISABLE (скажем так, они являются зависимой частью выражения при ENABLE и DISABLE );

· про создание и удаление индексов уже упоминалось;

· при изменении состояния из NOVALIDATE в VALIDATE выполняется проверка всех имеющихся в таблице данных, что может занять очень много времени. Наоборот, при приведении состояния Ограничения из VALIDATE в NOVALIDATE просто «забывается», что имеющиеся данные когда-то соответствовали Ограничению;

· перевод одиночного ограничения из состояния ENABLE NO VALIDATE в состояние ENABLE VALIDATE не блокирует чтения, записи или другие DDL операции, они могут быть выполнены параллельно.

И последние важные замечания.

  • При создании Ограничения можно указать в качестве атрибута, возможно ли в дальнейшем в ходе транзакции установить оператором SET CONSTRAINTS (см. выше) отложенную ( DEFERRED ) проверку данного Ограничения. По умолчанию выставляется NOT DEFERRABLE (что оно означает, думаю, понятно). После создания Ограничения изменить значение выставленного атрибута нельзя, кроме как пересоздав Ограничение, так что «семь раз подумай»!
  • INIT I ALLY является дополнительным атрибутом к DEFERRABLE, который может быть переписан оператором SET CONSTRAINT (опять см. выше), и который определяет поведение по умолчанию при срабатывании Ограничения с установленным атрибутом DEFERRABLE. При создании Ограничения по умолчанию выставляется INIT I ALLY IMMEDIATE , тогда срабатывание Ограничения будет происходить каждый раз при выполнении отдельного оператора, при выставлении INIT I ALLY DEFERRED срабатывание Ограничения будет отложено до окончания каждой транзакции. Для NOT DEFERRABLE такого дополнительного атрибута не требуется, так как он является INIT I ALLY IMMEDIATE по определению.
  • Выражение RELY. По умолчанию при изменении Ограничения (с помощью ALTER TABLE или MODIFY constraint ) выставляется NORELY . А означает оно следующее: стоит ли Oracle’у принимать в расчёт Ограничение, находящееся в состоянии NOVALIDATE, для перезаписи запроса? RELY активирует существующее Ограничение в режиме (здесь в документации SQL Reference используется слово « mode », хотя я уже привык к слову «state» из Concepts , – по-видимому, писали разные люди, позабыв договориться о терминах) NOVALIDATE, что позволит переписать запрос, который иначе мог быть переписанным только с Ограничением в режиме VALIDATE. Примерно так. Подробнее – в следующий раз.
  • EXCEPTIONS INTO определяет схему и таблицу, в которую будут внесены ROWID, нарушающие Ограничение при изменении его (Ограничения) состояния. Если имя схемы и таблицы не указать, то будет предполагаться, что нужно использовать таблицу с именем EXCEPTIONS в текущей схеме.
Читать еще:  Win 10 ввод в домен

Создание ограничений в SQL Server 2005(Урок 3)

Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных, т. е. ограничить возникновение в базе данных некорректных или противоречивых данных вследствие добавления, изменения или удаления какой- либо записи, например, ввод отрицательной цены или количества товара. Существует четыре типа целостности данных: доменная, сущностная, ссылочная и пользовательская (или бизнес-правила). Рассмотрим основные инструменты, предоставляемые в SQL Server для их реализации.

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

Использование проверочных ограничений

Ограничения на проверку используются для ограничения данных, принимаемых полем, даже если они имеют корректный тип. Например, поле Zip (почтовый индекс) имеет тип nchar(5), т.е. чисто теоретически оно может принимать буквы. Это может стать проблемой, поскольку не существует почтовых индексов с буквами. Рассмотрим, как создать ограничение на проверку, запрещающее вводить в это поле буквы.

  1. В контекстном меню папки «Ограничения» таблицы Customer выберите команду «Создать ограничение».
  2. В открывшемся окне «Проверочные ограничения» заполните следующие поля:
    • Имя: CK_Zip
    • Выражение: ([zip] like ‘[0-9][0-9][0-9][0-9][0-9]’). Данное выражение описывает ограничение, принимающее пять символов, которыми могут быть только цифры от 0 до 9.
    • Описание: Ограничение на значения почтового индекса
  3. Щелкните на кнопке «Закрыть» и закройте конструктор таблиц (он был открыт, когда вы начали создавать ограничение) с сохранением изменений.

Задание для самостоятельной работы: Создайте ограничения для полей InStock таблицы Product и Qty, Price таблицы OrdItem, запрещающие ввод в них отрицательных значений. В данном случае выражение проверки будет иметь вид (Имя поля > 0) для полей Qty, Price и (Instock>=0) для столбца InStock.

Использование значений по умолчанию

Установка для полей значений по умолчанию это отличный способ избавить пользователя от излишней работы, если значения этих полей во всех записях, как правило, принимают одни и те же значения. Так в таблице заказов Order вполне логично определить по умолчанию значение поля OrdDate (дата заказа) в виде текущей даты. В этом случае при добавлении записи о новом заказе в случае пропуска этого поля оно будет автоматически заполняться значением системной даты. Для создания такого свойства выполните следующие шаги:

  1. Раскройте папку «Столбцы» таблицы Order и в контекстном меню поля «OrdDate» выберите команду «Изменить».
  2. В свойстве столбца «Значение или привязка по умолчанию» введите getdate(). Эта функция T-SQL возвращает текущую системную дату.
  3. Щелкните на кнопке Сохранить и выйдите из конструктора таблиц.

Задание для самостоятельной работы: Установите для поля InStock (количество единиц продукта на складе) таблицы Product в качестве значения по умолчанию ноль.

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

Создание первичных ключей

Первичный ключ используется для обеспечения гарантии уникальности каждой записи в таблице. Он состоит из одного (простой ключ) или нескольких (составной ключ) столбцов с гарантированно уникальными значениями для каждой записи таблицы. Если пользователь попытается ввести в поля первичного ключа дублирующее значение будет сгенерирована ошибка и модификация данных будет отменена.

В качестве примера создадим первичный ключ для таблицы Customer. В данном случае идеальным кандидатом на роль первичного ключа выступает столбец IdCust, поскольку значения, содержащиеся в нем, являются уникальными по определению (для него установлено свойство identity). Следует отметить, что в качестве первичного ключа могут быть взяты и реальные атрибуты клиента, например, ИНН, номер страхового свидетельства, серия и номер паспорта вместе взятые (пример составного ключа), но использование различных разновидностей, так называемых, суррогатных ключей (identity, uniqueidentifier) обеспечивает большую степень сущностной целостности (поскольку реальные атрибуты могут все же со временем измениться) и является распространенной практикой. Для создания первичного ключа в таблице Customer выполните следующие шаги:

  1. В контекстном меню таблицы Customer выберите команду «Проект».
  2. В окне конструктора таблиц щелкните правой кнопкой мыши на поле IdCust и выберите команду «Задать первичный ключ» или нажмите кнопку на панели инструментов. Обратите внимание на то, что слева от поля IdCust теперь отображается значок ключа, указывающий, что поле является первичным ключом.
  3. Закройте конструктор таблиц с сохранением изменений

Задание для самостоятельной работы: Аналогичным образом создайте первичные ключи для остальных таблиц в соответствие с ниже приведенной таблицей.

Использование ограничений на уникальность

Между ограничениями первичного ключа и ограничениями на уникальность существует два отличия. Первое состоит в том, что первичные ключи используются вместе с внешними ключами для обеспечения целостности ссылок (рассматривается в следующем разделе). Второе отличие заключается в том, что ограничения на уникальность позволяют вставлять в его поля пустые значения (null), чего нельзя делать с первичными ключами. Во всем остальном они служат одной цели – обеспечить уникальность данных, вставляемых в поле. Ограничение на уникальность следует использовать в тех случаях, когда нужно гарантировать, что дублирующие значения не будут добавляться в поле, не являющееся частью первичного ключа, в частности, все потенциальные ключи должны быть организованы в виде ограничений уникальности. Хорошим примером такого поля, требующего ограничение на уникальность, является поле ИНН или серия и номер паспорта, поскольку эти поля должны быть уникальными у каждого человека. Такого идеального кандидата на роль уникального ограничения в нашей таблице Customer нет. Поэтому создадим его по полю Phone, которое также повторяться у разных клиентов не должно.

  1. Для открытия конструктора таблиц в контекстном меню таблицы Customer выберите команду «Проект». На панели инструментов нажмите на кнопку «Управление индексами и ключами» .
  2. В открывшемся окне «Индексы и ключи» щелкните кнопку «Добавить» и введите следующие параметры для нового уникального ключа:
    • Столбцы: Phone
    • Тип: Уникальный ключ
    • (Имя): CK_Phone
  • Закройте конструктор таблиц с сохранением изменений.
  • Задание для самостоятельной работы: Аналогичным образом создайте ограничение уникальности по полю CityName таблицы City, чтобы обеспечить отсутствие в справочнике городов с одинаковыми названиями, а также по полю Description таблицы Product, чтобы иметь возможность отличить один товар от другого.

    Обеспечение целостности ссылок

    Сейчас в базе данных Sales имеются пять таблиц, которые тесно взаимосвязаны между собой и соответственно данные содержащиеся в них должны быть согласованы и непротиворечивы. Например, в таблице Order не должно быть записей о заказах для клиента, данные о котором отсутствуют в таблице Customer. Чтобы гарантировать отсутствия в базе данных таких записей необходимо обеспечить целостность ссылок.

    Суть обеспечения целостности ссылок очевидна из названия: данные в одной таблице, ссылающиеся на данные из другой таблицы, защищены от некорректного обновления. В терминологии SQL Server это называется декларативной ссылочной целостностью и достигается путем связывания первичного ключа одной из таблиц с внешним ключом другой таблицы (создается так называемое ограничение внешнего ключа).

    Внешний ключ используется в комбинации с первичным для связывания двух таблиц по общему столбцу (столбцам). К примеру, можно связать таблицы Customer и Order по столбцу IdCust, который присутствует в обеих таблицах. Поскольку поле IdCust таблицы Customer является его первичным ключом можно использовать поле IdCust таблицы Order в качестве внешнего ключа, который свяжет эти две таблицы. После организации такого ограничения будет невозможно добавить запись в таблицу Order, если в таблице Customer нет записи с соответствующим значением IdCust. Кроме того, при отсутствии каскадирования (рассматривается в следующем разделе) невозможно удалить запись из таблицы Customer при наличии связанных с ней записей в таблице Order, поскольку нельзя оставлять заказ без информации о клиенте. Для создания описанного ограничения внешнего ключа в Management Studio выполните следующие шаги:

      В контекстном меню папки «Ключи» таблицы Order выберите команду «Создать внешний ключ…».

  • В открывшемся окне «Отношения внешнего ключа» заполните следующие поля:
    • (Имя): FK_Order_Customer
    • Спецификация таблиц и столбцов: Для заполнения данного блока щелкните на кнопке с многоточием и в появившемся окне «Таблицы и столбцы» в качестве таблицы первичного ключа выберите Customer, а полей связи — IdCust.
    Читать еще:  Настройка групповой политики в домене
  • Закройте все открывшиеся окна с сохранением изменений.
  • Использование каскадной ссылочной целостности

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

    Настроить правила каскадирования можно при создании ограничения внешнего ключа в окне «Отношения внешнего ключа» изменяя значения параметров «Обновить правило» и «Удалить правило» блока «Спецификация INSERT и UPDATE». Оба этих параметра могут содержать четыре значения, описанные в следующей таблице.

    Задание для самостоятельной работы: Создайте ограничение внешнего ключа FK_OrdItem_Order в таблице OrderItem для связи таблиц Order и OrderItem по полю IdOrd. При этом настройте правило каскадного удаления, установив в качестве параметра «Спецификация INSERT и UPDATEУдалить правило» значение «Каскадом», что приведет к автоматическому удалению всех товаров из заказа при удалении самого заказа.

    Использование диаграмм баз данных

    Диаграммы базы данных представляют собой графическое отображение схемы (целиком или частично) базы данных с таблицами и столбцами, а также связей между ними. Создадим диаграмму базы данных:

    1. В контекстном меню папки «Диаграммы базы данных» выберите команду «Создать диаграмму базы данных».
    2. В диалоговом окне «Добавление таблиц» выберите все таблицы и нажмите на кнопку «Добавить».
    3. Добавив таблицы, щелкните на кнопке «Закрыть» и вы увидите созданную диаграмму базы данных (на рисунке представлен окончательный вид диаграммы: некоторые связи у вас могут отсутствовать).

    Используя диаграмму базы данных ограничения внешнего ключа можно создавать значительно быстрее: лишь перетаскивая поля из одной таблицы в другую. В качестве примера создадим внешний ключ в таблице Customer по полю IdCity для связи с таблицей City:

    1. Выделите в таблице City поле IdCity и, не отпуская кнопку мыши, перетащите его на поле IdCity таблицы Customer.
    2. В диалоговых окнах «Таблицы и столбцы» и «Отношение внешнего ключа» примите настройки по умолчанию.
    3. Сохраните диаграмму базы данных под именем ILM.
    4. Расположите таблицы в канонической форме (главные таблицы выше подчиненных) в соответствии с вышеприведенным рисунком.

    Задание для самостоятельной работы: Аналогичным образом создайте связь между таблицами Product и OrdItem по полю IdProduct. Окончательный список связей между таблицами со всеми их характеристиками представлен в следующей таблице:

    Задание для самостоятельной работы: После настройки всех ограничений можно наполнить таблицы данными. Для этого в контекстном меню таблицы выберите команду «Открыть таблицу» и появившейся в рабочей области вкладке введите новые записи, заполняя все необходимые столбцы. В процессе внесения данных проверьте работоспособность всех созданных ранее ограничений:

    • Ограничений проверки: попробуйте ввести в поле Zip (почтовый индекс) таблицы Customer нечисловые значения, а в поля InStock таблицы Product и Qty, Price таблицы OrdItem — отрицательные.
    • Значений по умолчанию: убедитесь, что при пропуске полей OrdDate и InStock таблиц Order и Product для них устанавливаются значения по умолчанию в виде текущей системной даты и нуля соответственно.
    • Ограничений первичного и уникального ключа: попробуйте ввести в таблицы записи с дублирующими значениями первичного или уникального ключа.
    • Ограничений внешнего ключа: попробуйте ввести несогласованные данные в связанные таблицы, например, заказ для несуществующего клиента или удалить запись из любой главной таблицы при наличии связанных записей в подчиненной при отсутствии правил каскадирования.
    • Правил каскадирования: убедитесь, что при удалении записи из таблицы Order все связанные записи из таблицы OrdItem удаляются автоматически.

    Ограничения для доменов полей;

    Каждый столбец имеет собственный домен — некоторый набор до­пустимых значений.

    Стандарт SQL предусматривает два различных меха­низма определения доменов.

    Первый состоит в использовании предло­жения CHECK, позволяющего задать требуемые ограничения для столбца или таблицы в целом, а второй предполагает применение оператора CREATE DOMAIN.

    Соблюдение бизнес-правил.Обновления данных в таблицах могут быть ограничены существую­щими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE

    3.Фраза CONSTRAINTпозволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.

    4. Первичный ключ таблицы должен иметь уникальное непустое зна­чение в каждой строке. Стандарт SQL позволяет задавать подобные тре­бования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз.

    Однако существует возможность гарантировать уникальность значений и для лю­бых ключей таблицы, что обеспечивает ключевое слово UNIQUE. Кроме того, при определении альтернативных ключей рекомен­дуется использовать и спецификаторы NOT NULL.

    5. Обязательные данные.Для некоторых столбцов требуется наличие конкретного и допустимого значения, отличного от значения NULL. Для задания ограничений подобного типа стандарт SQL предусматривает использование спецификации NOT NULL.

    6. Ссылочная целостность.Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания строк дочерней таблицы, со­держащей этот внешний ключ, со строкой родительской таблицы, содер­жащей соответствующее значение первичного или потенциального ключа.

    Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY а фраза REFERENCESопределяет имя роди­тельской таблицы.

    При использовании этого предложения система откло­нит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значе­ние внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы.

    Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблице, то эти действия зависят от правил поддержки ссылочной целостности, указанных во фразах on update и ON delete предложения FOREIGN key.

    При определении таблицы предложение FOREIGN KEY может указы­ваться произвольное количество раз.

    Определитель MATCHпозволяет уточнить способ обработки значе­ния null во внешнем ключе.

    7. Если пользователь предпринимает попытку удалить из ро­дительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

    CASCADE —выполняется удаление строки из родительской шблицы, сопровождающееся стоматическим удалением всех ссылаюшихся на нее строк дочерней таблицы;

    SET NULL —выполняется удаление строки из родительской таблицы. а во внешние ключи всех ссылающихся на нее строк дочерней таб­лицы записывается значение null;

    SET DEFAULT —выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;

    NO ACTION —операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании и внешнего ключа фраза ON DELETE опущена. Те же самые правила применяются в языке SQL и тогда, когда значе­ние потенциального ключа родительской таблицы обновляется.

    Самый элементарный синтаксис оператора создания таблицы может быть следующим:

    CREATE TABLE имя_таблицы

    (имя_столбца тип_данных [NULL | NОТ NULL ] [. n]);

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

    Напомним: значение NULLотличается от пробела или нуля: к нему прибегают, когда необходимо указать, что дан­ные недоступны, опущены или недопустимы. Если указан параметр NULL, помещение значений NULL в столбец разрешено. Если указано ключевое слово NOT NULL, то будут отклонены любые попытки поместить значение NULL в данный столбец.

    По умолчанию стандарт SQL предполагает наличие ключевого словаNULL.

    Выше приведен упрощенный вариант оператора CREATE TABLE стандарта SQL. Его полная версия приводится при обсуждении вопросов ограничения целостности данных.

    Пример 2. Создаем таблицу Товарыв соответствии со структурой, определенной на практических занятиях. В эту таблицу добавим новое вычисляемое поле Цена_ед_ товара.

    CREATE TABLEТовары

    ID_товара INT IDENTITY Not Null, /*нумерация начнется с 1*/

    Название_товара VARCHAR(50) Not Null,

    Категория VARCHAR(50) Not Null,

    Ед_измерения VARCHAR(10) Not Null,

    Цена_ед_товара AS Поставки.Цена_ед_товара*1.2 /*вычисляемый столбец*/

    Теперь рассмотрим более развернутый вариант синтаксиса команды create table.

    Пример 3. Синтаксис команды create table

    CREATE TABLE [имя БД . [вдаделец] . ] имя таблицы

    [ [ DEFAULT ]

    | [IDENTITY [(нач.значение, приращение) [NOT FOR REPLICATION] ]]]

    [ROWGUIDCOL]

    [COLLATE ]

    [NULL | NOT NULL] [ ]

    | [column_name AS computed_column_expression]

    Ссылка на основную публикацию
    Adblock
    detector