Цілісність даних icon

Цілісність даних




Скачати 221.26 Kb.
НазваЦілісність даних
Дата конвертації31.03.2013
Розмір221.26 Kb.
ТипДокументи
1. /Конспект лекций/Использование SQL Server Management Studio.doc
2. /Конспект лекций/Лекция 0. Знайомство з SQL Server 2005.doc
3. /Конспект лекций/Лекция 1. Створення й керування базою даних SQL Server (short).doc
4. /Конспект лекций/Лекция 2. Синтаксичн_ елементи мови.doc
5. /Конспект лекций/Лекция 3. Виб_рка даних з бази даних.doc
6. /Конспект лекций/Лекция 4. Модиф_кац_я даних у базах даних SQL Server.doc
7. /Конспект лекций/Лекция 5. Збережен_ процедури.doc
8. /Конспект лекций/Лекция 5. Створення тригер_в _ керування ними ними.doc
9. /Конспект лекций/Мова SQL.Doc
10. /Конспект лекций/Создание триггеров и управление ими.doc
11. /Конспект лекций/Ц_л_сн_сть даних (short).doc
12. /Лабораторна робота малий формат.doc
Что такое sql server
Лекція Основи роботи з Microsoft sql| Server| 2005 Питання лекції Основні
Лекція Створення І керування базою даних sql server
Синтаксичні елементи мови Transact-sql
Вибірка даних з бази даних sql server
Модифікація даних у базах даних sql server
Збережені процедури
Створення|створіння| тригерів|трігерів| і керування ними (Тут додати що таке тригер і як він використовується)
Курс лекцій ( частина 1 ) для студентів спеціальності
Создание триггеров и управление ими
Цілісність даних
Методичні рекомендації для виконання лабораторних робіт для студентів спеціальності

Цілісність даних

У таблицях SQL Server може бути визначений ряд властивостей різного типу, що забезпечують цілісність даних. До них выдносяться типи даних, визначення NOT NULL і DEFAULT, властивості IDENTITY, обмеження CONSTRAINT, правила RULE, тригери TRIGER і індекси (INDEX).


Забезпечення цілісності даних

Забезпечення цілісності даних гарантує їхня якість. Припустимо, що ви створили в базі даних таблицю Customers. Значення стовпця Cust_ID повинне унікально ідентифікувати кожного покупця, відомості про яке занесені в таблицю. Таким чином, якщо значення Cust_ID якогось покупця дорівнює 438, те ні для якого іншого покупця це значення не може бути таким же. Далі припустимо, що є стовпець Cust_Rating, у якому визначається рейтинг покупців - у діапазоні від 1 до 8. У цьому випадку стовпець Cust_Rating не повинен допускати уведення ні числа 9, ні яких-небудь інших значень, крім цифр із інтервалу від 1 до 8. В обох випадках для забезпечення цілісності даних варто застосовувати один з методів, підтримуваних SQL Server.


Серед методів SQL Server, призначених для забезпечення цілісності даних, - визначення NOT NULL і DEFAULT, властивості IDENTITY, обмеження, правила, тригери й індекси. У главі 4 деякі з них уже згадувалися, тут же приводиться їхній короткий опис. Деякі властивості таблиці, наприклад визначення NOT NULL і DEFAULT, іноді вважають обмеженнями особливого типу. Однак відповідно до завдань цього навчального курсу, вони розглядаються окремо від обмежень.


Типи даних

Тип даних - це атрибут, що визначає тип (символьний, целочисленный, двійковий і т.д.) даних, які можуть зберігатися в полі, параметрі або змінної. SQL Server підтримує несколькосистемных типів даних, на основі яких можна створювати користувальницькі типи даних. Системні типи даних визначають всі типи даних, які можна застосовувати в SQL Server. Типи забезпечують цілісність даних, оскільки вводять данные, що, повинні відповідати типу, заданому для об'єкта. Наприклад, не можна зберігати прізвище в стовпці, для якого визначений тип даних datetime, оскільки стовпець із таким типом даних допускає лише уведення дат.


Визначення NOT NULL

Задаючи можливість уведення в поле порожніх значень, ви визначаєте, чи можуть у цьому стовпці таблиці зберігатися порожні значення. Порожнє значення відрізняється від нуля, пробілу або символьного рядка нульової довжини, наприклад ” ” Порожнє значення означає, що інформація не уведена, тобто значення не відомо або не визначене. Можливість уведення в стовпець порожніх значень задається при визначенні цього стовпця під час створення таблиці або її модифікацій. Через складності, пов'язаних з обробкою порожніх значень в SQL Server, у визначенні стовпців як допускають, так і не порожніх значень, що допускають уведення, завжди варто використати ключові слова NULL або NOT NULL. Якщо стовпець допускає порожні значення, використається ключове слово NULL, якщо немає - NOT NULL.


Визначення DEFAULT

Значення за замовчуванням задають значення, які буде використані, якщо при додаванні рядка значення поля не задано. Визначення DEFAULT створюють при визначенні таблиці (як частина цього визначення) або додають до вже існуючої таблиці. У будь-якому стовпці дозволене тільки одне визначення DEFAULT.


Властивості IDENTITY

У будь-якій таблиці дозволяється створити єдиний стовпець, що містить сгенерированные системою послідовні значення, що унікально ідентифікують кожний рядок таблиці. Значення стовпця з ідентифікатором звичайно унікальні в межах тієї таблиці, у якій вони визначаються. Інакше кажучи, не виключено, що значення стовпця ідентифікатора в різних таблиць можуть збігатися. Однак у таких ситуаціях звичайно не виникає проблем, оскільки значення ідентифікатора, як правило, використаються в контексті однієї таблиці, а стовпці з ідентифікаторами з різних таблиць не зв'язані.


Обмеження (CONSTRAINT)

Обмеження дозволяють визначати, яким образом SQL Server автоматично забезпечує цілісність даних. Обмеження визначають правила, що перевіряють припустимі значення стовпців, і являють собою стандартні механізми забезпечення цілісності. Обмеження переважніше тригерів, правил або умовчань. Їх також застосовує оптимізатор запитів для створення високопродуктивних планів виконання запитів.


Правила (RULE)

Правила виконують ряд функцій, аналогічних функціям обмеження CHECK, і застосовуються для забезпечення сумісності з попередніми версіями. Переважніше використати обмеження CHECK, які є стандартним способом обмеження значень поля. До того ж обмеження CHECK більше чіткі, чим правила. До стовпця дозволяється застосовувати лише одне правило, у той час як в одного стовпця може бути визначено кілька обмежень CHECK. Обмеження CHECK задаються в складі оператора CREATE TABLE, тоді як правила створюються як окремі об'єкти, які потім привязываются до стовпця.

Спочатку варто створити правило за допомогою оператора CREATE RULE. Після цього за допомогою системної збереженої процедури spbindrule його прив'язують до стовпця або користувальницького типу даних. Докладніше про використання CREATE RULE або sp bindrule - у довіднику по Transact-SQL в SQL Server Books Online.


Тригери (TRIGER)

Тригери - це особливий клас збережених процедур, операторів, що запускають автоматично при виконанні, UPDATE, INSERT або DELETE для таблиці або подання. Тригери являють собою потужний інструмент, застосовуваний для автоматичної реалізації правил-бізнесів-правил при модифікації даних. Тригери здатні розширити логікові перевірки цілісності, реалізовану обмеженнями, умовчаннями й правилами SQL Server (хоча у всіх випадках, коли обмеження здатні забезпечити необхідну функціональність, варто використати їх замість тригерів).


Індекси (INDEX)

Індекс - це структура, що впорядковує значення одного або декількох стовпців таблиці в базі даних. Індекс надає покажчики на значення, що зберігаються в заданих стовпцях таблиці, після чого впорядковує їх відповідно до заданого порядку сортування. Використання базою даних індексу багато в чому нагадує обіг читача до предметного покажчика книги: вона переглядає індекс у пошуках деякого значення, а потім по покажчику знаходить рядок, у якій утримується необхідне значення. Унікальний індекс забезпечує унікальність значень стовпця.


Типи цілісності даних

SQL Server підтримує чотири типи цілісності даних: цілісність сутностей, доменну цілісність, посилальну цілісність і цілісність, обумовлену користувачем. Чотири зазначених у цьому курсі типу вважаються головними типами цілісності даних.


Цілісність сутностей

Цілісність сутностей визначає рядок таблиці як унікальний екземпляр деякої сутності. Цілісність сутностей забезпечує цілісність стовпця з ідентифікатором або первинним ключем таблиці (за допомогою індексів, обмежень UNIQUE, PRIMARY KEY або властивостей IDENTITY).


Доменна цілісність

Доменна цілісність гарантує наявність у деякому стовпці тільки припустимих значень. Можна забезпечувати доменну цілісність, обмежуючи тип (за допомогою типів даних), формат (за допомогою обмежень CHECK і правил) або діапазон припустимих значень (за допомогою обмежень FOREIGN KEY і CHECK, визначень DEFAULT, визначень NOT NULL і правил).


Посилальна цілісність

Посилальна цілісність забезпечує схоронність зв'язків між таблицями при додаванні або видаленні записів. В SQL Server посилальна цілісність заснована на зв'язках між зовнішніми й первинними ключами або між зовнішніми й унікальними ключами (через обмеження FOREIGN KEY і CHECK). Посилальна цілісність гарантує погодженість значень ключа у зв'язаних таблицях. Подібна погодженість вимагає відсутності посилань на неіснуючі значення й погоджену зміну посилань на ключ у всій базі даних при зміні самого ключа.

При забезпеченні посилальної цілісності SQL Server запобігає наступним діям користувачів:

  • додавання записів у зв'язану таблицю, якщо немає необхідного запису в головній таблиці;

  • зміна значень у головній таблиці, у результаті якого у зв'язаній таблиці залишаться ォзависліサ запису;

  • видалення записів з головної таблиці при наявності зв'язаних записів у зовнішній таблиці.


Цілісність, обумовлена користувачем

Цілісність, обумовлена користувачем, дозволяє визначати деякі бізнес правила, що не попадають у жодну іншу категорію цілісності. Всі категорії цілісності підтримують цілісність, обумовлену користувачем (всі обмеження в операторі CREATE TABLE, що діють на рівні стовпців і таблиць, збережених процедур і тригерів).


Вправа 1. Визначення властивостей, що гарантують цілісність даних

У цій вправі ви розглянете кілька таблиць із бази даних Pubs і визначите властивості, що забезпечують цілісність даних. Щоб виконати ця вправа, необхідно зареєструватися як системний адміністратор на комп'ютері під керуванням Windows 2000 Server.


Визначення властивостей таблиці Employee

1. Відкрийте SQL Query Analyzer і підключитеся до локального сервера.

2. Уведіть на панелі Editor у вікні Query наступні команди Transact-SQL:

USE Pubs

GO

sp_help Employee

Цей оператор визначає базу даних, у якій перебуває потрібна таблиця, і за допомогою системної збереженої процедури sp_help виводить для перегляду відомості про таблицю Employee з бази даних Pubs.

3. Виконаєте цей оператор Transact-SQL. На вкладці Grids панелі Results з'являються відомості про таблицю Employee.

4. Переглянете результати на вкладці Grids. Знайдіть відомості про типи даних, можливості уведення порожніх значень, визначення DEFAULT, властивість IDENTITY, індекси й обмеження.

До яких типів ставляться обмеження, певні в таблиці Employee?

Які стовпці таблипы Employee допускають порожні значення?


Визначення властивостей таблиці Publishers

1. На панелі Editor вікна Query уведіть, а потім виконаєте наступну команду Transact-SQL:

sp_help Publishers

На вкладці Grids панелі Results виводяться відомості про таблицю Publishers.

2. Прокрутите результати на вкладці Grids. Знайдіть відомості про типи даних, можливості уведення порожніх значень, визначень обмеження DEFAULT, властивість IDENTITY, індекси й обмеження.

До яких типів ставляться обмеження, певні в таблиці Publishers?

Який стовпець таблиці Publishers містить ідентифікатор?

Для яких стовпців таблиці Publishers визначений символьний тип даних?


Визначення властивостей таблиці Titles

1. Уведіть на панелі Editor у вікні Query, а потім виконаєте наступну команду Transact-SQL:

sp_help Titles

На вкладці Grids панелі Results виводяться відомості про таблицю Titles.

2. Переглянете результати на вкладці Grids. Знайдіть відомості про типи даних, можливості уведення порожніх значень, визначення DEFAULT, властивість IDENTITY, індекси й обмеження.

Для яких стовпців таблиці Titles визначений користувальницький тип даних?

Скільки стовпців з таблиці Titles допускають порожні значення?

3. Закрийте SQL Query Analyzer.

Резюме

У таблицях SQL Server можна визначити ряд властивостей різного типу, що гарантують цілісність даних. До цих властивостей ставляться типи даних, визначення NOT NULL і DEFAULT, властивості IDENTITY, обмеження, правила, тригери й індекси. SQL Server підтримує чотири типи цілісності даних: цілісність сутностей, доменну цілісність, посилальну цілісність і цілісність, обумовлену користувачем. Цілісність сутностей визначає рядок як унікальний екземпляр сутності в деякій таблиці. Доменна цілісність - це адекватність елементів даного поля. Посилальна цілісність забезпечує схоронність зв'язків, певних між таблицями, при додаванні або видаленні рядків. Цілісність, обумовлена користувачем, дозволяє визначати деякі правила-бізнеси-правила, що не попадають ні в яку іншу категорію цілісності.


Реалізація обмежень, що забезпечують цілісність

Обмеження - це властивість, призначена таблиці або стовпцю таблиці, що забороняє уведення в зазначений стовпець (або стовпці) неприпустимих значень. Наприклад, обмеження UNIQUE або PRIMARY KEY запобігають уведенню значень, які вже існують, обмеження CHECK забороняє уведення значень, незбіжних з умовою пошуку, а обмеження FOREIGN KEY забезпечує зв'язок між двома таблицями.

Загальні відомості про обмеження, що забезпечують цілісність

Обмеження дозволяють визначати способи, за допомогою яких SQL Server 2005 буде автоматично забезпечувати цілісність бази даних. Обмеження визначають правила, що мають відношення до припустимих значень полів, і є стандартним механізмом, що забезпечує цілісність.

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

  • обмеження для стовпця задається як правила визначення стовпця і застосовується тільки до цього стовпця;

  • обмеження для таблиці оголошується незалежно від визначення стовпців і застосовується до декількох стовпців таблиці.

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


SQL Server підтримує чотири головних класи обмежень: PRIMARY KEY, UNIQUE, FOREIGN KEY і CHECK. Розглянемо їх детальніше.


Обмеження PRIMARY KEY

У таблиці звичайно є стовпець (або комбінація стовпців), значення яких унікально ідентифікують кожний рядок таблиці. Цей стовпець (або стовпці) називаються первинним ключем таблиці і забезпечує цілісність сутностей таблиці. Можна створити первинний ключ, визначивши обмеження PRIMARY KEY при створенні або зміні таблиці. У таблиці може бути тільки одне обмеження PRIMARY KEY, причому стовпець, що бере участь в обмеженні, не повинен допускати порожніх значень. Оскільки обмеження PRIMARY KEY гарантують унікальність даних, вони часто визначаються як стовпці з ідентифікатором. Коли в таблиці наявні певні обмеження PRIMARY KEY, SQL Server 2000 забезпечує унікальність даних, створюючи унікальний індекс для стовпців первинного ключа. Цей індекс також забезпечує швидкий доступ до даних при використанні первинного ключа в запитах.

Якщо обмеження PRIMARY KEY визначене для декількох стовпців, то в одному стовпці значення можуть повторюватися, але кожна комбінація значень всіх стовпців, які входять у визначення обмеження PRIMARY KEY, повинна бути унікальною.


Створення обмежень PRIMARY KEY

Обмеження PRIMARY KEY створюють одним з наступних способів:

  • під час створення таблиці (у складі визначення таблиці);

  • додаючи обмеження до існуючої таблиці, якщо немає іншого обмеження PRIMARY KEY.


Існуюче обмеження PRIMARY KEY можна модифікувати або видалити. Наприклад, в обмеженні PRIMARY KEY при бажанні можна змінити стовпці, на які посилається обмеження, порядок стовпців, ім'я індексу, ознаку кластерізації або коефіцієнт заповнення. Змінити довжину стовпця, для якого певне обмеження PRIMARY KEY, не можна.

Щоб модифікувати обмеження PRIMARY KEY за допомогою TransactSQL, спочатку варто видалити існуюче визначення обмеження PRIMARY KEY, а потім створити нове визначення. Наступний оператор CREATE TABLE створює таблицю Tablel і визначає в якості її первинного ключа стовпець Col_1:

CREATE TABLE Table1

(

Col_1 INT PRIMARY KEY,

Col_2 VARCHAR(30)

)

Tе ж саме обмеження визначають за допомогою визначення PRIMARY KEY на рівні таблиці:

CREATE TABLE Table1

(

Col_l INT.

Col_2 VARCHAR(30),

CONSTRAINT table_pk PRIMARY KEY (Col_l)

)

Ще один спосіб - додати обмеження PRIMARY KEY до існуючої таблиці за допомогою оператора ALTER TABLE:

ALTER TABLE Tablel

ADD CONSTRAINT table_pk PRIMARY KEY (Col_1)

Коли до існуючого стовпця (або стовпців) таблиці додається обмеження PRIMARY KEY, SQL Server 2000 перевіряє наявні в стовпцях дані, щоб гарантувати їхню відповідність правилам, які діють для первинних ключів:

  • відсутність порожніх значень;

  • відсутність повторюваних значень.

Якщо обмеження PRIMARY KEY додається до стовпця, у якому є повторювані або порожні значення, SQL Server повертає помилку й не додає обмеження. Неможливо додати обмеження PRIMARY KEY з порушенням вищевказаних правил. SQL Server автоматично створює унікальний індекс, щоб забезпечити відповідність вимозі унікальності, що висуває обмеження PRIMARY KEY.

Увага! Неможливо видалити обмеження PRIMARY KEY, якщо на нього посилається обмеження FOREIGN KEY з іншої таблиці. Спочатку необхідно видалити обмеження FOREIGN KEY.


Обмеження UNIQUE

Обмеження UNIQUE дозволяє заборонити уведення повторюваних значень у деякі стовпці, що не беруть участь у формуванні первинного ключа. Незважаючи на те, що унікальність забезпечують обидва обмеження - і UNIQUE, і PRIMARY KEY, у деяких ситуаціях замість обмеження PRIMARY KEY варто використовувати обмеження UNIQUE. Так поводяться, якщо:

  • стовпець (або комбінація стовпців) не є первинним ключем. Для таблиці можна визначити кілька обмежень UNIQUE і лише одне обмеження PRIMARY KEY;

  • стовпець допускає порожні значення. Обмеження UNIQUE дозволяється визначати для стовпців, які допускають порожні значення, у той час як обмеження PRIMARY KEY можна визначити тільки для стовпців, які не допускають порожніч значень. Обмеження FOREIGN KEY також може посилатися на обмеження UNIQUE.


Створення обмежень UNIQUE

Обмеження UNIQUE створюється так само, як PRIMARY KEY:

  • при створенні таблиці (у складі визначення таблиці);

  • шляхом додавання до існуючої таблиці за умовою, що стовпці, що становлять обмеження UNIQUE, містять тільки порожні й унікальні значення. У таблиці може бути кілька обмежень UNIQUE.

Для створення обмеження UNIQUE призначені ті ж оператори Transact-SQL, що і для створення обмеження PRIMARY KEY. Просто замініть слова PRIMARY KEY словом UNIQUE. Як і у випадку з PRIMARY KEY, визначення обмеження UNIQUE дозволяється модифікувати або видаляти. Коли обмеження UNIQUE додається до існуючого стовпця (або стовпців), у якому є повторювані або порожні значення, SQL Server 2000 перевіряє (за замовчуванням) наявні в стовпцях дані, щоб гарантувати унікальність всіх значень, крім порожніх. Якщо обмеження UNIQUE додається до стовпця, у якому є повторювані значення, SQL Server повертає помилку і не додає обмеження. SQL Server автоматично створює індекс UNIQUE, щоб забезпечити відповідність вимозі унікальності, що висуває обмеження UNIQUE. Тому при спробі вставити повторюваний рядок SQL Server повертає повідомлення про помилку, у якому говориться, що порушено обмеження UNIQUE, і не додає рядок у таблицю.

Обмеження FOREIGN KEY

Зовнішній ключ - це стовпець або комбінація стовпців, що використовуються для встановлення і забезпечення зв'язку між даними двох таблиць. Зв'язок між двома таблицями встановлюється шляхом додавання до однієї з таблиць стовпця (стовпців) і визначення для нього обмеження FOREIGN KEY. Цей стовпець містить значення первинного ключа із іншої таблиці. У таблиці може бути кілька обмежень FOREIGN KEY. Наприклад, у таблиці Titles з бази даних Pubs є зв'язок з таблицею Publishers, оскільки існує логічний зв'язок між книгами й видавництвами. Стовпець pub_id у таблиці Titles відповідає стовпцю з первинним ключем таблиці Publishers. Стовпець pub_id у таблиці Titles - це зовнішній ключ для таблиці Publishers.


Можна створити зовнішній ключ, визначивши обмеження FOREIGN KEY під час створення або зміни таблиці. На додаток до PRIMARY KEY, обмеження FOREIGN KEY також може посилатися на стовпці обмеження UNIQUE в інших таблицях. Обмеження FOREIGN KEY може містити порожні значення. Однак якщо кожний із стовпців складеного обмеження FOREIGN KEY містить порожні значення, перевірка цього обмеження не виконується.

Обмеження FOREIGN KEY може посилатися на стовпці інших таблиць цієї бази даних або на інші стовпці цієї ж таблиці (таблиці, що посилаються самі на себе).


Хоча основне призначення обмеження FOREIGN KEY — контроль даних, які можуть бути збережені в таблиці із зовнішнім ключем, воно також виявляє зміну даних у таблиці з первинним ключем. Наприклад, якщо з таблиці Publishers видалити рядок з відомостями про видавництво, у той час як ідентифікатор цього видавництва використовується для книг у таблиці Titles, посилальна цілісність між цими двома таблицями порушиться. Книги, які випущені вилученим видавництвом, “зависнуть” у таблиці Titles без посилання на дані таблиці Publishers. Обмеження FOREIGN KEY не допускає подібної ситуації. Це обмеження забезпечує посилальну цілісність, гарантуючи, що в таблицю з первинним ключем неможливо внести такі зміни, які зроблять недійсними посилання на дані таблиці із зовнішнім ключем. Спроба видалення рядка з таблиці з первинним ключем або зміни значення первинного ключа закінчиться невдачею, якщо видаляєме або змінюване значення первинного ключа відповідає значенню обмеження FOREIGN KEY іншої таблиці. Щоб успішно змінити або видалити рядок, спочатку необхідно видалити або змінити дані зовнішнього ключа з таблиці із зовнішнім ключем, у результаті чого зовнішній ключ буде пов'язаний з іншими даними первинного ключа.


Створення обмежень FOREIGN KEY

Обмеження FOREIGN KEY створюють одним з наступних методів:

  • під час створення таблиці (у складі визначення таблиці);

  • додаванням до існуючої таблиці за умовою, що обмеження FOREIGN KEY пов'язане з існуючим обмеженням PRIMARY KEY або UNIQUE з іншої (або цієї ж) таблиці.

Створені обмеження FOREIGN KEY дозволяється модифікувати й видаляти. Наприклад, при бажанні можна замінити стовпці, на які посилається обмеження FOREIGN KEY. Довжину стовпця, для якого визнечене обмеження FOREIGN KEY, змінити не можна.

Щоб модифікувати обмеження FOREIGN KEY за допомогою Transact-SQL, спочатку варто видалити існуюче визначення обмеження FOREIGN KEY, а потім створити нове визначення.

Наступний оператор CREATE TABLE створює таблицю Table1 і визначає для стовпця Col_2 обмеження FOREIGN KEY, що посилається на стовпець Employee1, який є в таблиці Employees стовпцем з первинним ключем:

CREATE TABLE Tablel

(

Col_1 INT PRIMARY KEY,

Col_2 INT REFERENCES Employees(Employee1)

)

Це ж обмеження можна створити, визначивши його на рівні таблиці:

CREATE TABLE Tablel

(

Col_l INT PRIMARY KEY,

Col_2 INT,

CONSTRAINT Col_2_fk FOREIGN KEY (Col_2) REFERENCES Employees (Employeel)

)

Обмеження FOREIGN KEY до існуючої таблиці додають за допомогою оператора ALTER TABLE:

ALTER TABLE Tablel

ADD CONSTRAINT col_2_fk FOREIGN KEY (Col_2)

REFERENCES Employees (Employeel)

Коли обмеження FOREIGN KEY додається до існуючого стовпця (або стовпцям), SQL Server 2000 перевіряє (за замовчуванням) наявні в стовпцях дані, щоб гарантувати, що всі значення, крім порожніх, існують у стовпцях з обмеженням PRIMARY KEY або UNIQUE, на які посилається FOREIGN KEY. Однак можна зробити так, щоб SQL Server не звіряв дані стовпця з новим обмеженням, і змусити SQL Server додати нове обмеження незалежно від даних стовпця. Ця можливість корисна в тих випадках, коли існуючі дані вже відповідають новому обмеженню FOREIGN KEY або коли бізнес-правило вимагає, щоб обмеження діяло, починаючи із цього моменту. Однак слід дотримуватися обережності при додаванні обмеження без перевірки існуючих даних, оскільки ця дія відбувається в обхід контролю SQL Server, що забезпечує цілісність дані таблиці.


Обмеження CHECK

Обмеження CHECK, забезпечують доменну цілісність шляхом обмеження значень, які дозволено вводити в стовпець. Цим вони схожі на обмеження FOREIGN KEY. Відмінність між ними полягає в способі визначення припустимих значень. Обмеження FOREIGN KEY одержують список припустимих значень із іншої таблиці, а обмеження CHECK визначають їх на основі логічного виразу, у яке не входять дані з іншого стовпця. Наприклад, можна обмежити діапазон значень стовпця з відомостями про зарплату, створивши обмеження CHECK, що допускає тільки значення від $15000 до $100000. Ця функція запобігає уведенню значень, які виходять за межі нормального діапазону зарплати. Можна створити обмеження CHECK за допомогою будь-якого логічного виразу, що повертає TRUE або FALSE залежно від використовуваного логічного оператора. От як виглядає логічний вираз, що допускає тільки значення з інтервалу $15 000 - $100 000:

salary >= 15000 AND salary <= 100000

До одного стовпця дозволяється застосувати кілька обмежень CHECK, які перевіряються в порядку їхнього створення. Крім того, можна застосувати одне обмеження CHECK до декількох стовпців, якщо створити його на рівні таблиці. Наприклад, засобами обмеження CHECK, певного для декількох стовпців, вдається підтвердити, що в будь-якому рядку, де значення поля країна дорівнює “USA”, у поле штат внесене значення, що складається із двох символів. Ця функція дозволяє перевірити в одному місці кілька умов.


Створення обмежень CHECK

Обмеження CHECK створюють одним з наступних способів:

  • під час створення таблиці (у складі визначення таблиці);

  • додають до існуючої таблиці.

Створені обмеження CHECK дозволено модифікувати й видаляти. Наприклад, можна модифікувати вираз, який використовуэться в обмеженні CHECK, визначеному для стовпця або таблиці.

Щоб модифікувати обмеження CHECK за допомогою Transact-SQL, спочатку варто видалити існуюче визначення обмеження CHECK, а потім створити нове визначення.

У цьому прикладі створюється таблиця Tablel і визначається для стовбця со1_2 обмеження CHECK, що обмежує введення значень з діапазоном 0 - 1000:

CREATE TABLE Table1

(

Col_l INT PRIMARY KEY,

Col_2 INT

CONSTRAINT limit_amount CHECK (Col_2 BETWEEN 0 AND 1000),

Col_3 VARCHAR(50)

)

Це обмеження також можна визначити на рівні таблиці:

CREATE TABLE Tablel

(

Col_l INT PRIMARY KEY,

Col_2 INT,

Col_3 VARCHAR(5O),

CONSTRAINT limit_amount CHECK (Col_2 BETWEEN 0 AND 1000)

)

Щоб додати обмеження СНЕСК до існуючої таблиці, можна скористатися оператором ALTER TABLE:

ALTER TABLE Tablel

ADD CONSTRAINT limit_amount CHECK (Col_2 BETWEEN 0 AND 1000)

При додаванні до існуючої таблиці обмеження CHECK застосовується тільки до нових даних або до всіх даних таблиці, включаючи існуючі. За замовчуванням обмеження CHECK застосовується як до існуючих, так і до всіх нових даним. Можливість застосувати обмеження тільки до нових даних корисна, якщо існуючі дані вже відповідають новому обмеженню CHECK або якщо бізнес-правило вимагає, щоб обмеження застосовувалося, тільки починаючи із цього моменту.

Однак потрібно бути обережним при додаванні обмеження без перевірки існуючих даних, оскільки ця дія відбувається в обхід контролю SQL Server, що забезпечує цілісність дані таблиці.


Вправа 2. Додавання обмежень в існуючі таблиці

У цій вправі ви додасте обмеження до таблиць, створеним у базі даних BookShopDB. Для цього буде потрібно модель даних і бізнесу-правила, певні у вправі 3 глави 3. Зверніть увагу, що в кожній таблиці повинен бути первинний ключ. У деяких випадках він складається із двох стовпців. Крім того, для деяких таблиць будуть потрібні зовнішні ключі, а для таблиці Customers - обмеження CHECK. Бізнеси-правила й модель даних підкажуть, коли варто визначати ці обмеження. Наприклад, одне з бізнесів-правил, що ставляться до таблиці Books, говорить, що в стовпці Condition ID повинні бути значення зі стовпця ConditionID таблиці BookCondition. Це правило вимагає визначити для стовпця ConditionID таблиці Books обмеження FOREIGN KEY, що посилається на стовпець CondiiionID таблиці BookCondition, Щоб виконати ця вправа, необхідно зареєструватися як адміністратор на комп'ютері під керуванням Wincows 2000 Server.


Додавання обмеження PRIMARY KEY до таблиці Authors

1. Звернетеся до бізнесів-правил і структури бази даних і знайдіть стовпець (або стовпці) таблиці Authors, для яких варто визначити обмеження PRIMARY KEY. На даному етапі процесу розробки бази даних необхідно визначитися, які стовпці треба сконфигурировать як первинний ключ. Помнете, що обмеження PRIMARY KEY задають для стовпця (стовпців), значення яких унікально ідентифікують кожний рядок таблиці. Для якого стовпця (або стовпців) варто визначити обмеження PRIMARY KEY?

2. Відкрийте SQL Query Analyzer і підключитеся до локального сервера.

3. Уведіть на панелі Editor у вікні Query наступний код на Transact-SQL:

USE BookShopDB

ALTER TABLE Authors

ADD CONSTRAINT authors_pk PRIMARY KEY (Authorl)

Цей оператор додає до стовпця AuthorID таблиці Authors з бази даних BookShopDB обмеження PRIMARY KEY (authors_pk).

4. Виконаєте оператор Transact-SQL. На вкладці Messages панелі Results виводиться повідомлення про успішне завершення команди.


Додавання обмеження PRIMARY KEY до таблиці BookAuthors

1. Звернетеся до бізнесів-правил і структури бази даних і знайдіть стовпець (або стовпці) таблиці BookAuthors, для яких треба визначити обмеження PRIMARY KEY Для якого стовпця (або стовпців) таблиці BookAuthors варто визначити обмеження PRIMARY KEY?

2. Уведіть на панелі Editor у вікні Query наступний код на Transact-SQL:

ALTER TABLE BookAuthors

ADD CONSTRAINT bookauthors_pk PRIMARY KEY (Authorl, Titlel)

Цей оператор додає до комбінації стовпців Authorl і Titlel обмеження PRIMARY KEY (bookauthors_pk)

3. Виконаєте оператор Transact-SQL. На вкладці Messages панелі Results виводиться повідомлення про успішне завершення команди.


Додавання обмеження PRIMARY KEY до інших таблиць бази даних BookShop DB

1. За допомогою бізнесів-правил і структури бази даних знайдіть в інших таблицях бази дані стовпці, для яких варто визначити обмеження PRIMARY KEY.

2. Засобами SQL Query Analyzer додайте первинний ключ до всіх інших таблиць бази даних BookShopDB.

У таблиці BookOrders первинний ключ створюється із двох стовпців. В інших таблицях для первинного ключа можна використати один стовпець із ідентифікатором.

Які оператори Transact-SQL варто застосувати, щоб додати до інших таблиць обмеження PRIMARY KEY?

Тепер у кожній таблиці бази даних BookShopDB повинне бути певне обмеження PRIMARY KEY.


Додавання до таблиці BookAuthors обмеження FOREIGN KEY

1. Звернетеся до бізнес- правилам і структурі бази даних і знайдіть у таблиці BookAuthors

стовпець (або стовпці), для яких варто визначити обмеження FOREIGN KEY.

Помнете, що обмеження FOREIGN KEY установлює й забезпечує зв'язок між двома таблицями. Можна визначити, де повинні бути ці зв'язки, глянувши на бизнесправила й структуру бази даних. Для якого стовпця (або стовпців) таблиці BookAuthors варто визначити обмеження FOREIGN KEY?

2. Уведіть на панелі Editor у вікні Query наступний код на Transact-SQL:

ALTER TABLE BookAuthors

ADD CONSTRAINT authorid_fk FOREIGN KEY (Authorl)

REFERENCES Authors (Authorl)

Цей оператор додає до стовпця Authorl таблиці BookAuthors обмеження FOREIGN KEY (authorid_fk), що посилається на стовпець Authorl таблиці Authors.

3. Виконаєте оператор Transact-SQL. Вкладка Messages на панелі Results виводить повідомлення про успішне завершення команди.

4. На панелі Editor вікна Query уведіть і виконаєте наступний код на Transact-SQL:

ALTER TABLE BookAuthors

ADD CONSTRAINT titleid.fk FOREIGN KEY (Titlel)

REFERENCES Books (Titlel)

На вкладці Messages панелі Results виводиться повідомлення про успішне завершення команди.


Додавання обмеження FOREIGN KEY до таблиць Books, BookOrders, Orders і Employees

1.За допомогою бізнесів-правил і структури бази даних знайдіть у таблицях Books, BookOrders, Orders і Employees стовпці, для яких варто визначити обмеження FOREIGN KEY.

2. Засобами SQL Query Analyzer додайте зовнішній ключ до таблиць Books, BookOrders, Orders і Employees.

У таблиці BookOrders варто визначити обмеження FOREIGN KEY у кожному стовпці. У таблиці Orders необхідно додати обмеження FOREIGN KEY до кожного)1 із чотирьох стовпців, що посилаються на інші таблиці. У таблицях Books і Employees треба визначити по одному обмеженню FOREIGN KEY. Які оператори Transact-SQL варто використати, щоб додати до таблиць Books, BookOrders, Orders і Employees эграничения FOREIGN KEY?

Тепер у відповідних таблицях бази даних BookShopDB повинні бути певні обмеження FOREIGN KEY.


Додавання до таблиці Customers обмеження CHECK

1. Звернетеся до бізнесів-правил і структури бази даних і знайдіть у таблиці Customers стовпець (або стовпці), для якого варто визначити обмеження CHECK. Помнете, що обмеження CHECK забезпечує доменну цілісність, обмежуючи значення, які можна ввести в стовпець. Для якого стовпця (або стовпців) таблиці Customers варто визначити обмеження CHECK?

2. Уведіть на панелі Editor у вікні Query наступний код на Transact-SQL:

ALTER TABLE Customers

ADD CONSTRAINT checknames_ck CHECK

(FirstNarne NOT LIKE 'unknown' OR LastName NOT LIKE 'unknown' )

Цей оператор додає обмеження CHECK, що гарантує, що хоча б в одному зі стовпців FirstName і LastName буде значення, відмінне від unknown (заданого визначенням DEFAU LT для обох стовпців). Інакше кажучи, два поля однієї й того ж рядка не можуть одночасно містити значення unknown.

3. Виконаєте оператор Transact-SQL. На вкладці Messages панелі Results виводиться повідомлення про успішне завершення команди.


Додавання обмеження CHECK до таблиць Authors і Books

1. За допомогою бізнесів-правил і структури бази даних знайдіть у таблицях Authors і Books стовпці, для яких треба визначити обмеження CHECK. Для яких стовпців таблиць Authors і Books варто визначити обмеження CHECK?

2. Уведіть на панелі Editor у вікні Query наступний код на Transact-SQL:

USE BookShopDB

ALTER TABLE Authors

ADD CONSTRAINT authors1_ck

CHECK (YearBorn LIKE ('[1-2][0,6-9][0-9][0-9]')

OR (YearBorn = 'N/A'))

ALTER TABLE Authors

ADD CONSTRAINT authors2_ck

CHECK (YearBorn NOT LIKE '[1 ][0][0-9][0-9]')

ALTER TABLE Authors

ADO CONSTRAINT authors3_ck

CHECK (YearBorn NOT LIKE '[2][6-9][0-9][0-9]')

ALTER TABLE Authors

ADD CONSTRAINT authors4_ck

CHECK (YearDied LIKE ('[1-2][0,6-9][0-9HO-9]')

OR (YearDied = 'N/A'))

ALTER TABLE Authors

ADD CONSTRAINT authors5_ck

CHECK (YearDied NOT LIKE '[1][0][0-9][0-9]')

ALTER TABLE Authors

ADD CONSTRAINT authors6_ck

CHECK (YearDied NOT LIKE '[2][6-9][0-9][0-9]')

ALTER TABLE Books

ADD CONSTRAINT books1_ck

CHECK (PubDate LIKE ('[1-2][0,6-9][0-9][0-9]1)

OR (PuDDate = 'N/A'))

ALTER TABLE Books

ADD CONSTRAINT books2_ck

CHECK (PubDate NOT LIKE '[1][0][0-9][0-9]')

ALTER TABLE Books

ADD CONSTRAINT books3_ck

CHECK (PubDate NOT LIKE '[2][6-9][0-9][0-9]')

Цей код додає обмеження CHECK до стовпців YearBorn, YearDied і PubDate, щоб задати діапазон значень років, які дозволено вводити в ці стовпці.

3. Виконаєте цей код Transact-SQL. На вкладці Messages панелі Results виводиться повідомлення про успішне завершення команди.


Як переконатися, що обмеження додані до таблиць

1. На панелі Editor вікна Query уведіть і виконаєте наступний код на Transact-SQL:

sp_helpconstraint Authors

На вкладці Grids панелі Results виводяться відомості про таблицю Authors.

2. Прокрутите вкладку Grids до розділу з відомостями про обмеження. У результатах запиту повинні бути перераховані всі обмеження, створені для цієї таблиці: одне обмеження PRIMARY KEY і чотири визначення DEFAULT.

3. Вивчите обмеження в інших таблицях за допомогою системної збереженої процедури sp_helpconstraint. Упевніться, що створено всі необхідні обмеження.

4. Закрийте SQL Query Analyzer.


Резюме

Обмеження визначають правила, що перевіряють припустимі значення стовпців, і є стандартними механізмами забезпечення цілісності. Обмеження можна визначати як для стовпців, так і для таблиць. Обмеження для таблиць варто використати, коли в обмеження входить кілька стовпців. SQL Server підтримує чотири головних типи обмежень: PRIMARY KEY, UNIQUE, FOREIGN KEY і CHECK. Обмеження PRIMARY KEY, що визначається для стовпця або комбінації стовпців, гарантує, що значення цих стовпців унікально ідентифікують кожний рядок у таблиці. Обмеження UNIQUE схоже на обмеження PRIMARY KEY, воно гарантує, що в стовпці, що не беруть участь у формуванні первинного ключа, не будуть уведені повторювані значення. Обмеження FOREIGN KEY - це стовпець або комбінація стовпців, що використається для встановлення й забезпечення зв'язку між даними двох таблиць. Обмеження CHECK забезпечують доменну цілісність, обмежуючи значення, які можна ввести в стовпець.


Закріплення матеріалу

  1. Які властивості бази даних SQL Server використовуються для забезпечення цілісності даних?

  2. У чому розходження між визначеннями DEFAULT і NOT NULL?

  3. Назвіть чотири типи цілісності даних, які підтримує SQL Server.

  4. У чому розходження між посилальною й доменною цілісністю?

  5. Який тип цілісності зберігає зв'язки, які існують між таблицями, при видаленні або уведенні записів?

  6. Які типи обмежень підтримує SQL Server?

  7. Скільки визначень обмеження PRIMARY KEY може входити у визначення таблиці?

  8. Коли замість обмеження PRIMARY KEY варто використовувати обмеження UNIQUE?

  9. Для чого SQL Server перевіряє існуючі дані, коли обмеження PRIMARY KEY додається до існуючого стовпця (або стовпців)?

  10. Що таке обмеження FOREIGN KEY і як його створити?

  11. Як обмеження CHECK визначає припустимі значення?



Схожі:

Цілісність даних iconІнформація щодо заповнення форми
Лист даних" служить формою внесення моніторингових даних; лист "Примітки" служить для внесення приміток по даних. Також, файл має...
Цілісність даних iconКонцепція типу даних
Структура даних, прості і складені типи даних, схема, приклади деяких простих типів, призначення
Цілісність даних iconПідготовка до контрольної роботи «Бази даних Access»
База даних – це структурована сукупність взаємопов’язаних даних певної предметної області (реальних об’єктів, процесів)
Цілісність даних iconТема. Робота з об'єктами бази даних у середовищі субд
Завдання. Здійснити пошук та впорядкування даних бази даних Бібліотека за визначеними умовами
Цілісність даних iconТема. Поняття моделі даних, бази даних
Обладнання та наочність: дошка, комп'ютер, презентація «Бази даних. Субд», інструкції з тб в комп'ютерному кабінеті
Цілісність даних iconЗгода на збір та обробку персональних даних
Головному управлінні освіти і науки уточнену інформацію та подавати оригінали відповідних документів для внесення моїх та моєї дитини...
Цілісність даних iconІнформація про володільця бази персональних даних  Юридична особа х  Резидент х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Цілісність даних iconІнформація про володільця бази персональних даних  Юридична особа Х  Резидент Х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Цілісність даних iconІнформація про володільця бази персональних даних  Юридична особа х  Резидент х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Цілісність даних iconІнформація про володільця бази персональних даних  Юридична особа Х  Резидент Х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Цілісність даних iconІнформація про володільця бази персональних даних  Юридична особа Х  Резидент Х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©te.zavantag.com 2000-2017
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи