Для розширення можливостей роботи з даними, що вводять у таблицю, цікавий механізм умов перевірки значень, або, як його ще часто називають - обмеження даних.
Обмеження даних - це частина визначень таблиці, що обмежує значення, припустимі до уведення в поля таблиці. До цього як обмеження на дані, розглянутого нами, минулого тільки тип і розмір значень, що вводять, які повинні бути сумісні з тими полями, у які ці значення містяться.
Обмеження дають більші можливості й, наприклад, обмовивши значення за замовчуванням (це значення, що автоматично уставляється в будь-яке поле таблиці, коли явне значення відсутнє в команді), команда INSERT для поля вже не буде вставляти NULL значення.
Коли створюється таблиця або змінюється її структура, можна встановлювати обмеження на значення, які можуть бути уведені в поля. Якщо це зроблено, SQL буде відкидати будь-які значення, які порушують обговорені критерії. Як уже було сказано раніше, є два основних типи обмежень - обмеження поля (атрибута) і обмеження таблиці (відносини). Нагадаємо, що розходження між ними в тім. Що обмеження поля застосовується тільки до певного поля, а обмеження таблиці - до груп з одного й більше полів.
Оголошення обмежень здійснюється в такий спосіб: обмеження поля міститься в кінець фрагмента команди, що повідомляє його ім'я після типу даних; обмеження таблиці міститься в кінець оголошення імені таблиці після останнього імені поля. Таким чином, повний синтаксис для команди CREATE TABLE такий:
CREATE TABLE <TABLE NAME>
<COLUMN NAME> <DATA TYPE> [(<SIZE>)]
<COLUMN CONSTRAINT>,
(<COLUMN NAME> <DATA TYPE> [(<SIZE>)]
<COLUMN CONSTRAINT>, ...
<TABLE CONSTRAINT (<COLUMN NAME>
[,<COLUMN NAME>])...);
Тут обмеження поля (COLUMN CONSTRAINT) застосовується до того поля, після чийого імені це обмеження треба. Назви полів у круглих дужках після обмеження таблиці (TABLE CONSTRAINT) - це поля, до яких застосоване дане обмеження.
Часто команду CREATE TABLE використають для того, щоб охоронити поле від влучення в нього NULL значень. Із цією метою використають обмеження NOT NULL, що може застосовуватися тільки як обмеження поля. Очевидно, що первинні ключі таблиці ніколи не повинні бути порожніми, оскільки це буде порушувати їхні функціональні можливості. Більше того, такі поля, як правило, вимагають певних унікальних значень.
Наприклад, якщо при створенні таблиці STUDENTS, помістити ключові слова NOT NULL відразу після типу даних для полів, у яких невизначене значення небажано, будь-яка спроба їхнього запису в ці поля буде відхилена. Якщо ж при створенні таблиці це не обговорено, то SQL уважає, що NULL значення дозволені. Отже, розглянемо команду:
CREATE TABLE STUDENTS
(SNUM INTEGER NOT NULL,
SFAM CHAR (20) NOT NULL,
SIMA CHAR (10),
SOTCH CHAR (15)
STIP DECIMAL);
У такій таблиці невизначене значення не може бути встановлене для полів SNUM й SFAM. При цьому важливо не забувати, що тепер для цих стовпців обов'язково повинне бути встановлене явне значення в команді INSERT.
При використанні команди ALTER TABLE для додавання нових стовпців до вже існуючої таблиці, можна поміщати обмеження стовпців типу NOT NULL. Однак якщо новому стовпцю встановлюється обмеження NOT NULL, що модифікує таблиця повинна бути порожній.
Часто в полі потрібно реалізувати обмеження, пов'язане з унікальністю значень - це навіть скоріше властивість даних у таблиці, і тому його більш логічно назвати обмеженням даних, а не просто обмеженням поля. Безсумнівно, унікальні індекси - один з найпростіших і найбільш ефективних методів приписання унікальності. Однак є можливість установити унікальність як обмеження стовпця.
Якщо необхідно бути впевненим у тім, що всі значення, уведені в поле, відрізняються друг від друга, то в обмеження поля при створенні таблиці поміщають ключове слово UNIQUE. При цьому СКБД відхилить будь-яку спробу уведення в це поле значення, що вже має місце в іншому рядку. Це обмеження може застосовуватися тільки до полів, для яких уже було оголошене обмеження NOT NULL, що цілком логічно - не має змісту дозволити одного запису мати значення NULL, а потім виключати інші рядки з NULL значеннями як дублікати.
Для ілюстрації цих можливостей, удосконалимо попередній приклад і заборонимо повторювані значення в поле SNUM, тому що в цій таблиці логічне використання його як первинний ключ:
CREATE TABLE STUDENTS
(SNUM INTEGER NOT NULL UNIQUE,
SFAM CHAR (20) NOT NULL,
SIMA. CHAR (10),
SOTCH CHAR (15)
STIP DECIMAL) ;
Нагадаємо, що поля, значення в які вимагають унікальності, однак не є первинними ключами, називаються ключами - кандидатами або унікальними ключами. Наприклад, можна було б передбачити обмеження унікальності для поля SFAM, тоді його можна було б уважати ключем - кандидатом, однак це не дозволило б зберігати в таблиці STUDENTS однофамільців.
В SQL можна визначити групу з декількох полів як унікальну за допомогою команди обмеження таблиці UNIQUE. Оголошення групи полів унікальної, принципово відрізняється від оголошення унікальним одного поля: у першому випадку це - комбінація значень, а в другому - індивідуальне значення кожного запису. При цьому зовсім не обов'язково, щоб у кожнім полі, що входить у групу, значення не були повторюваними. З іншого боку, якщо кожне з полів, що входять у комбінацію умови унікальності, саме по собі є унікальним, те великого змісту в такому обмеженні немає.
Наприклад, у таблиці успішності USP така ситуація повинна бути з парами значень номер студентського квитка - код предмета - окремий запис для кожної здачі студентом того або іншого навчального предмета не повинна існувати. Таким чином, при створенні таблиці USP скористаємося наступною командою:
CREATE TABLE USP
(UNUM INTEGER NOT NULL UNIQUE,
OCENKA INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, PNUM));
Зверніть увагу на те, що обоє поля в обмеженні таблиці UNIQUE ще до того ж використають обмеження стовпця NOT NULL.
До цього використалося обмеження UNIQUE або унікальні індекси в первинних ключах для приписання цим полям властивості унікальності. Однак SQL підтримує первинні ключі безпосередньо з обмеженням PRIMARE KEY.
За допомогою PRIMARY KEY можна обмежувати таблицю або окремі його стовпці. Це обмеження працює аналогічно UNIQUE, однак для даної таблиці повинен бути тільки один первинний ключ. Синтаксис і визначення його унікальності ті ж, що й для обмеження UNIQUE - первинні ключі не допускають NULL значень, отже, будь-яке поле, використовуване в обмеженні PRIMARY KEY, повинне вже бути оголошене NOT NULL. Розглянемо наступний приклад:
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
OCENKA INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, PNUM));
Як видно, найкраще поміщати обмеження PRIMARY KEY у поле, що утворює унікальний ідентифікатор рядка, і зберегти обмеження UNIQUE для поля або групи полів, які повинні бути унікальними логічно, а не використатися для ідентифікації рядків.
Обмеження PRIMARY KEY може також бути застосоване для групи полів, що становлять унікальну комбінацію значень.
Спробуємо передбачити для останнього приклада інший первинний ключ:
CREATE TABLE USP
(UNUM INTEGER NOT NULL UNIQUE,
OCENKA INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
PRIMARY KEY (SNUM, PNUM));
У цьому випадку первинним ключем є комбінація полів SNUM й PNUM. яка повинна бути унікальної із причин, викладеним вище. Очевидно, що ні перше, ні друге поле в цій таблиці не можна змусити не містити повторювані значення окремо, однак їхня комбінація є унікальна структура. І ще один важливий момент - як первинний ключ краще застосовувати поля числового (переважно - цілого) типу, тому що використання текстових полів може привести до неоднозначних результатів. Наприклад, не виключена ймовірність того, що при використанні як первинний ключ комбінації полів, що містять прізвище, ім'я та по батькові студента, у таблиці зможуть з'явитися більше однієї людини, у яких ці дані збігаються. Зрозуміло, що первинний ключ, що в одних випадках працює, а в інші - ні, не є прийнятним.
Зрозуміло, може існувати будь-яке число обмежень, які допускається встановлювати для даних, що вводять у таблиці БД. Для цього в SQL передбачене обмеження CHECK, що дозволяє поставити умову, відповідно до якого перевіряється значення, що вводить у таблицю, до того, як воно буде збережено. Це обмеження складається із ключового слова CHECK і предиката, що використає зазначене поле. Будь-яка спроба модифікувати або вставити значення поля, що робить цей предикат невірним, буде відхилена.
Розглянемо для приклада таблицю USP. Очевидно, що оцінка, отримана студентом, не повинна перевищувати 5 балів, тому можна при створенні цієї таблиці передбачити відповідну перевірку:
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
OCENKA INTEGER CHECK (OCENKA =< 5),
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, PNUM));
Загалом кажучи, можна використати CHECK для того, щоб визначати припустиме вводити значение, що. Наприклад, для поліпшення попереднього приклада, варто передбачити те, що в поле OCENKA можуть бути уведені тільки оцінки 1, 2, 3, 4 або 5. Тоді наступна команда дозволить виключити уведення інших значень і запобігти можливим помилкам:
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
OCENKA INTEGER CHECK IN(1, 2, 3, 4, 5),
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, PNUM));
Зверніть увагу на те, що команда ALTER TABLE дозволяє змінювати визначення таблиці, навіть коли вона перебуває у використанні. Однак зміна або видалення обмежень не завжди можливо. Більше надійним способом часом є створення нової таблиці зі зміненим обмеженням і заповнення її інформацією зі старої таблиці.
В SQL можна також використати CHECK як табличне обмеження. Це корисно в тих випадках, коли виникає необхідність включити більше одного поля в умову. Припустимо, що відмінні оцінки можуть бути виставлені тільки до певної дати, скажемо до 15/06/1999. Тоді скористаємося наступною командою:
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
OCENKA INTEGER,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
CHECK (OCENKA < 5
AND UDATE > 15/06/1999)) ;
У таку таблицю тепер можна ввести тільки оцінки нижче 5 балів для дат, пізніше 15/06/1999. При цьому, як можна бачити, два різні поля однієї й тієї ж рядки повинні бути перевірені для того, щоб визначити - вірний чи предикат ні. Крім того, не можна використати обмеження CHECK для того, щоб перевірити умова зв'язане відразу з декількома рядками таблиці, наприклад, що оцінки по дисципліні не нижче, ніж середня оцінка по всіх навчальних предметах.
Коли відбувається вставка запису в таблицю без вказівки значень для кожного поля, SQL повинен мати значення за замовчуванням для включення його в кожен запис, інакше команда не буде виконана. Найпоширенішим значенням за замовчуванням є NULL. Це значення за замовчуванням для будь-якого стовпця, якому не було дане обмеження NOT NULL.
В SQL є засіб DEFAULT (за замовчуванням), що вказує в команді CREATE TABLE аналогічно, як й обмеження поля. Майте на увазі, що значення DEFAULT не обмежувальної властивості - воно не обмежує дані, які можна вводити, а просто визначає, що повинне трапитися, якщо не уведено кожне з явних припустимих значень.
Наприклад, якщо більшість оцінок, що попадає в таблицю USP, це п'ятірки, то логічно вказати це як значення поля OCENKA за замовчуванням, тобто:
CREATE TABLE USP
(UNUM INTEGER NOT NULL PRIMARY KEY,
OCENKA INTEGER CHECK IN(1, 2, 3, 4, 5)
DEFAULT = 5,
UDATE DATE,
SNUM INTEGER NOT NULL,
PNUM INTEGER NOT NULL,
UNIQUE (SNUM, PNUM) );
Звичайно, використати значення за замовчуванням - це альтернатива для NULL, тому що невідоме значення невірно при будь-якому порівнянні (крім IS NULL), і такі записи виключаються за допомогою більшості предикатів. Іноді, коли потрібно бачити порожні значення полів, не обробляючи їх якимсь певним чином, варто встановити значення за замовчуванням нуль для числових або пробіл для текстових полів. Розходження в цьому випадку буде полягати в тім, що тепер такі записи будуть оброблятися так само. Як і будь-які інші значення. Це означає, що при вибірці даних ці записи будуть включені у висновок запиту, що часто є дуже корисним.
У цьому ж полі допускається використати обмеження UNIQUE або PRIMARY KEY, але тоді будь-яку вставляти строку, що, прийде модифікувати перш, ніж буде доданий який-небудь інший рядок із цією установкою за замовчуванням, що не зовсім зручно.
На практиці для вмісту окремих полів існують й інші обмеження. Наприклад, поле TFAM таблиці TEACHERS не повинне насправді містити будь-яке довільне прізвище. Наприклад, прізвище Смирнова є "неправильної", тому що не відповідає жодному викладачеві розглянутого Вуза. Формально мовою реляционной БД це означає, що доменом поля TFAM є не просто які-небудь строкові значення, і навіть не просто прізвища людей, а ряд прізвищ людей, що є викладачами цього Вуза.
У стандарті SQL2 визначення домена реалізоване як частина визначення БД. Відповідно до цього стандарту, домен є іменованою сукупністю значень даних і широко використається у визначенні БД, як додатковий тип даних. Домен створюється за допомогою команди CREATE DOMAIN, і, після оголошення, на нього можна посилатися, як на тип даних.
Таким чином, нами розглянуті кілька способів керування значеннями, які можуть бути уведені в поля таблиці. Ці засоби дозволять більш повно використати можливості SQL й уникати помилок при уведенні даних.
випадку буде полягати в тім, що тепер такі записи будуть оброблятися так само. Як і будь-які інші значення. Це означає, що при вибірці даних ці записи будуть включені у висновок запиту, що часто є дуже корисним.