15824

Создание ограничений в SQL Server 2005

Лекция

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

Создание ограничений в SQL Server 2005 Создание ограничений Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных т. е. ограничить возникновение в базе данных некорректных или п

Русский

2013-06-18

416 KB

6 чел.

Создание ограничений в SQL Server 2005

Создание ограничений

 

Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных, т. е. ограничить возникновение в базе данных некорректных или противоречивых данных вследствие добавления, изменения или удаления какой- либо записи, например, ввод отрицательной цены или количества товара. Существует четыре типа целостности данных: доменная, сущностная, ссылочная и пользовательская (или бизнес-правила). Рассмотрим основные инструменты, предоставляемые в 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 выполните следующие шаги:

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

 

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

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

 

 

  1.  Закройте все открывшиеся окна с сохранением изменений.

 Использование каскадной ссылочной целостности

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

 

Настроить правила каскадирования можно при создании ограничения внешнего ключа в окне «Отношения внешнего ключа» изменяя значения параметров «Обновить правило» и «Удалить правило» блока «Спецификация 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 удаляются автоматически.


 

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

35560. Доменные фурмы. Снижение потерь тепла от горячего дутья через внутренний конус (стакан) фурмы 616.5 KB
  В работах сообщается об успешной эксплуатации фурм с внутренними конусами из углеродистых и легированных сталей. О снижении теплопотерь от стальных конусов говорит тот факт [30] что замена материала внутреннего конуса толщина стенки 10 мм с меди на углеродистую и легированную сталь приводит к повышению температуры поверхности конуса со стороны горячего дутья со 108 до 300 и 5600С соответственно. Но со временем сложилось мнение [5] что конструкции фурм со стальными внутренними конусами недолговечны так как испытывая ударные...
35561. Профилактика зависимости от ПСИХОАКТИВНЫХ ВЕЩЕСТВ 2.45 MB
  Представленные в пособии материалы позволят тренеру составить детальный план тренинговых занятий с учетом потребностей целевой группы различного опыта и знаний подростков о вреде наркотиков. Основные понятия В ходе тренинга с участниками группы происходят изменения. Развитие или движение группы во времени обусловленное взаимодействием и взаимоотношениями членов группы между собой и с ведущим называют групповой динамикой. Групповая сплоченность формирование у участников чувства принадлежности к группе группового единства необходимое...
35562. ВЫЧИСЛИТЕЛЬНЫЕ МАШИНЫ, СИСТЕМЫ И СЕТИ 4.47 MB
  В учебном пособии изложены основные принципы схемотехнической и программной организации современных ЭВМ. Основное внимание уделено задачам организации ЭВМ на основе микропроцессоров фирмы Intel.
35563. ОБОРУДОВАНИЕ ДЛЯ ВТОРИЧНОЙ ОБРАБОТКИ МЕТАЛЛОВ И СПЛАВОВ 1.87 MB
  Рассмотрены источники образования и классификация вторичных отходов металлов описаны операции разделки и компактирования сепарации лома и отходов металлов приведены конструктивные схемы установок и оборудования для вторичной обработки металлов и сплавов. Источники образования и структура вторичных сырьевых ресурсов Ресурсы отходов цветных металлов и сплавов  это часть фонда металлов и сплавов перешедшая в категорию отходов к моменту на который определяется фонд. Оборотные отходы  часть отходов металлов и сплавов...
35564. Высокие технологии в металлургии. ч.1 Производство цветных металлов 1.14 MB
  Кратко изложена теория и практика современной металлургии меди никеля алюминия магния и титана. Металлургия меди 5 1.2 Свойства меди и области её применения 8 1.3 Сырье для получения меди 9 1.
35565. ТЕОРИЯ ПЕРЕХОДНЫХ ПРОЦЕССОВ. ТЕОРЕТИЧЕСКИЕ ОСНОВЫ ЭЛЕКТРОТЕХНИКИ 9.56 MB
  21 Операторные схемы замещения элементов цепи22 Законы Ома и Кирхгофа в операторной форме.38 Численное решение уравнения состояния явный метод Эйлера40 ЛЕКЦИЯ 5 Линейные электрические цепи при импульсных воздействиях Расчет реакции цепи на одиночный импульс воздействия метод наложения.42 Расчет реакции цепи на периодическое импульсное воздействия метод сопряжения интервалов . Дальнейшее состояние цепи называют установившимся процессом.
35566. ЭКСПЛУАТАЦИОННЫЕ СВОЙСТВА АВТОМОБИЛЕЙ 3.43 MB
  АТС и его эксплуатационные свойства4 Вопрос 2. Условия эксплуатации АТС. Тяговоскоростные свойства АТС ТСС АТС. Силы действующие на АТС9 Вопрос 5.
35567. Металлургия черных металлов 1.51 MB
  Дан расчет количества МНЛЗ обращено внимание на выбор типа и основных проектных характеристик МНЛЗ. Типы МНЛЗ и их применение. Расчет количества МНЛЗ для рассматриваемого примера. На обоих предприятиях установлена и внедрена в производство установка внепечной очистки сталей АКВОС на ОАО €Электросталь€ строится 5й СПЦ который планируется оборудовать двумя ДСП20 агрегатами внепечной очистки стали и МНЛЗ.
35568. ТЕХНОЛОГИЯ ПРОИЗВОДСТВА СТАЛИ В ЭЛЕКТРИЧЕСКИХ ПЕЧАХ 1.01 MB
  Курс лекций Технология производства стали в электрических печах ГОУ СПО Красносулинский металлургический колледж г. Данное методическое пособие является кратким курсом лекций по дисциплине Технология производства стали в электрических печах для студентов 3 курса специальности 150108 Порошковая металлургия композиционные материалы покрытия СОДЕРЖАНИЕ АННОТАЦИЯ. ТЕХНОЛОГИЧЕСКИЕ ПРОЦЕССЫ ПРОИЗВОДСТВА СТАЛИ.