Мал. 12.2. Ієрархія видів обмежень цілісності
Обмеження цілісності. що входять до визначення домену, успадковуються всіма стовпцями, визначеними на цих доменах, і є обмеженнями цих стовпців. Крім того, в визначення стовпця можуть входити визначення додаткових обмежень. Обмеження цілісності. що входять до визначення стовпчика (включаючи обмеження, успадковані з визначення домену), є обмеженнями таблиці, до складу визначення якої входить визначення даного стовпчика. Крім того, в визначення таблиці можуть входити визначення додаткових обмежень.
Але ієрархія видів обмежень цілісності цим не вичерпується. Обмеження цілісності. що входять до визначення таблиці (включаючи явні і успадковані від визначення доменів обмеження стовпців), являють собою обмеження бази даних. частиною якої є дана таблиця. Крім того, можуть визначатися додаткові обмеження бази даних. У стандарті SQL такі додаткові обмеження бази даних називаються ASSERTION. а ми їх будемо називати загальними обмеженнями цілісності.
Визначення загальних обмежень цілісності
Для визначення загального обмеження цілісності служить оператор CREATE ASSERTION. задається в наступному синтаксисі:
У визначенні таблиці EMP містилося обмеження стовпця EMP_BDATE:
У логічному умови цього загального обмеження вибирається мінімальне значення стовпця EMP_BDATE (дата народження найстарішого службовця). Значним умовного виразу буде false в тому і тільки в тому випадку, якщо серед службовців є хоча б один, який народився до зазначеної дати.
Тепер формулюємо у вигляді загального обмеження цілісності обмеження таблиці EMP PRO_EMP_NO. яке визначалося наступним чином:
(Над одним проектом не може працювати більше 50 службовців).
Ось формулювання еквівалентного загального обмеження цілісності:
Логічний вираз цього обмеження може приймати тільки значення true і false. Внутрішній оператор вибірки групує рядки таблиці EMP таким чином, що в одну групу потрапляють всі рядки з однаковим значенням стовпця PRO_NO. Потім ці групи фільтруються за умовою розділу HAVING. і залишаються тільки групи, що включають понад 50 рядків. У результуючій таблиці містяться рядки з одного стовпчика, що містить значення PRO_NO груп, що залишилися. Предикат NOT EXISTS приймає значення true тоді і тільки тоді, коли ця результуюча таблиця не містить жодного рядка, т. Е. Немає жодного проекту, в якому працює більше 50 службовців.
Покажемо, як можна сформулювати у вигляді загального обмеження цілісності обмеження зовнішнього ключа. Наприклад, наведемо таку еквівалентну формулювання для визначення зовнішнього ключа PRO_NO. що входить до складу визначення таблиці EMP:
У вигляді загального обмеження цілісності це може виглядати наступним чином:
Логічний вираз цього обмеження виглядає досить складним і потребує пояснення. Умова вибірки оператора SELECT на рядку (2) складається з двох частин, пов'язаних через AND. Перша частина відфільтровує ті рядки таблиці EMP. у яких в стовпці PRO_NO міститься NULL. Якщо цей стовпець містить NULL у всіх рядках таблиці, то результуюча таблиця оператора вибірки на рядку (2) буде порожній, і значенням предиката NOT EXISTS буде true. т. е. обмеження задовольняється.
Тепер припустимо, що в таблиці EMP знайшлася рядок emp. в стовпці PRO_NO якій міститься значення, відмінне від NULL. Назвемо це значення cand_pro_no. Для нього обчислюється друга частина умови вибірки оператора SELECT на рядку (2). Оператор вибірки на рядку (3) вибирає всі рядки таблиці PRO. значення стовпця PRO_NO яких дорівнює cand_pro_no. Якщо для даного значення cand_pro_no знайшлася хоча б одна така рядок, то результуюча таблиця оператора вибірки на рядку (3) буде непорожній, і значенням предиката NOT EXISTS на рядку (3) буде false. Відповідно, всі умова вибірки першого оператора SELECT прийме значення false. і рядок зі значенням cand_pro_no в стовпці PRO_NO буде відфільтрована. 16 Це означає, що cand_pro_no є допустимим значенням зовнішнього ключа.
Якщо ж знайдеться хоча б один рядок таблиці EMP з таким значенням cand_pro_no стовпчика PRO_NO. що в таблиці PRO бракуватиме жодного рядка, значення стовпця PRO_NO якої дорівнювало б цього cand_pro_no. то результуюча таблиця оператора вибірки на рядку (3) буде порожній, і значенням предиката NOT EXISTS на рядку (3) буде true. Тоді все умова вибірки першого оператора SELECT прийме значення true. і цей рядок таблиці EMP буде пропущена в результуючу таблицю. Значним предиката NOT EXISTS буде false. т. е. обмеження не задовольняється.
Ми свідомо привели таке докладний пояснення не тільки для того, щоб прояснити сенс умовного вираження загального обмеження цілісності FK_PRO_NO. але і щоб дати зрозуміти, у що реально вироджується проста синтаксична конструкція визначення зовнішнього ключа. Як показує досвід, багато початківці проектувальники SQL-орієнтованих баз даних думають, що посилальні обмеження так само легко підтримувати, як визначати.
Нарешті, сформулюємо загальне обмеження цілісності, що складається в тому, що ніякої менеджер проекту не повинен мати сумарний загальний дохід, більший сумарного доходу керівника відділу, в якому працює цей менеджер.
У логічному вираженні цього обмеження використовується оператор вибірки SELECT. в розділі переліку таблиць (FROM) вперше в цьому курсі використовується кілька таблиць. Такі запити в SQL називаються запитами з сполуками, і ми скористаємося нагодою, щоб пояснити на прикладі (звичайно, попередньо), як їх слід розуміти відповідно до стандарту мови SQL.
Отже, в розділі FROM оператора вибірки, використовуваного в логічному умови цього обмеження, через кому перераховані чотири елементи - EMP EMP1. EMP EMP2. DEPT і PRO. Вираз виду EMP ANOTHER_NAME означає застосування свого роду операції перейменування. Усередині запиту Стовпчики "екземпляра" EMP мають "кваліфіковані" імена виду ANOTHER_NAME.column_name. де column_name позначає ім'я існуючого стовпця таблиці EMP.
Обчислення оператора вибірки починається з того, що формується розширене декартовій твір всіх таблиць, зазначених в розділі FROM. В даному випадку схема результуючої таблиці розділу FROM міститиме такі імена стовпців: EMP1.EMP_NO. EMP1.EMP_NAME. EMP1. EMP_BDATE. EMP1. EMP_SAL. EMP1.EMP_BONUS. EMP1. DEPT_NO. EMP1. PRO_NO. EMP2.EMP_NO. EMP2.EMP_NAME. EMP2. EMP_BDATE. EMP2. EMP_SAL. EMP2.EMP_BONUS. EMP2. DEPT_NO. EMP2. PRO_NO. DEPT.DEPT_NO. DEPT.DEPT_EMP_NO. DEPT.DEPT_TOTAL_SAL. DEPT.DEPT_MNG. PRO .PRO_NO. PRO .PRO_TITLE. PRO .PRO_SDATE. PRO .PRO_DURAT. PRO .PRO_MNG. PRO_DESC. Для зручності назвемо цю "широку" таблицю ALL_TOGETHER. 17 Не слід сприймати цей і наступні абзаци як опис того, як насправді виконуються подібні запити в SQL-серверах. Це найбільш прямолінійний і малоефективний спосіб виконання запиту (хоча, в принципі, його можна застосовувати і на практиці). Ми вибрали цей спосіб опису, оскільки він максимально відповідає підходу до опису семантики мови SQL, що застосовується в стандарті мови. До речі, основною відмінністю більш практичних способів виконання запитів з з'єднанням є прагнення до того, щоб уникнути явного декартова твори.
Умова розділу WHERE складається з чотирьох частин, пов'язаних через AND. Обговоримо їх послідовно. Після перевірки умови EMP1.EMP_NO = PRO .PRO_MNG в таблиці ALL_TOGETHER залишаться всі службовці-менеджери проектів разом зі своїми проектами в комбінації з усіма можливими відділами і всіма можливими службовцями (назвемо цю відфільтровану таблицю ALL_TOGETHER_STEP1). Після перевірки умови EMP1.DEPT_NO = DEPT.DEPT_NO в таблиці ALL_TOGETHER_STEP1 залишаться всі службовці-менеджери проектів разом зі своїми проектами і разом з описом своїх відділів в комбінації з усіма можливими службовцями (назвемо цю відфільтровану таблицю ALL_TOGETHER_STEP2). Після перевірки умови DEPT.DEPT_MNG = EMP2.EMP_NO в таблиці ALL_TOGETHER_STEP2 залишаться всі службовці-менеджери проектів разом зі своїми проектами, разом з описом своїх відділів і разом з керівниками цих відділів (по одному рядку для кожного допустимого поєднання "проект-менеджер_проекта-отдел_менеджера_проекта- руководітель_отдела_менеджера_проекта "). Назвемо цю відфільтровану таблицю ALL_TOGETHER_STEP3. Легко бачити, що після перевірки умови EMP1.EMP_SAL + EMP1.EMP_BONUS> EMP2.EMP_SAL + EMP2.EMP_BONUS в таблиці ALL_TOGETHER_STEP3 можуть залишитися тільки рядки проект-менеджер_проекта-отдел_менеджера_проекта-руководітель_отдела_менеджера_проекта. в яких сумарний дохід менеджера проекту перевищує сумарний дохід керівника відділу, де працює менеджер проекту. Якщо хоча б одна така рядок існує, то результат оператора вибірки буде непустою, значенням предиката NOT EXISTS буде false. і тим самим обмеження цілісності PRO_MNG_CONSTR буде порушено.