Цей документ описує підтримку зовнішніх ключів SQL, реалізованих в SQLite версії 3.6.19.
Перша глава присвячена загальній концепції зовнішніх ключів в SQLite, пояснює її за допомогою прикладів і термінологічних визначень, необхідних для розуміння даного документа. Другий розділ покроково описує, що необхідно зробити для включення підтримки зовнішніх ключів в SQLite (за замовчуванням вони відключені). Наступна глава (третя) описує, які індекси повинен створювати користувач для використання зовнішніх ключів, і які слід створити для підвищення ефективності роботи зовнішнього ключа.
Глава 4 описує просунуті можливості, пов'язані з зовнішніми ключами, підтримуваними в SQLite. А в розділі 5 описується, як працюють команди ALTER і DROP TABLE при наявності зовнішніх ключів. У заключній 6 чолі перераховуються нереалізовані можливості і обмеження поточної реалізації.
Цей документ не містить повного опису синтаксису для створення зовнішніх ключів, що використовується в SQLite. Воно може бути знайдено як частина документації по вираженню CREATE TABLE.
1. Загальні відомості про обмеження зовнішнього ключа
Зовнішні ключі в SQL використовуються для примусового підтримання зв'язків між таблицями в актуальному стані. Розглянемо, наприклад, схему бази даних, створену наступними командами SQL:
На жаль, якщо користувач редагує базу даних з використанням зовнішніх інструментів, або якщо в додатку є баг, з'являється можливість вставляти в таблицю «track» рядки, які не мають відповідних рядків в таблиці «artist». Або можна видалити рядки з таблиці «artist» тим самим створюючи «висячі» рядки в таблиці «track», для яких не залишається відповідних рядків в таблиці «artist».
Така ситуація може призводити до логічних помилок в додатках, програмним збоїв або зробить розробку додатків складнішою.
Одним з рішень є додавання до схеми бази даних зовнішнього ключа SQL, який би примусово забезпечував збереження зв'язків між таблицями «artist» і «track». Щоб це зробити, потрібно модифікувати декларацію таблиці «track» додавши до нього визначення зовнішнього ключа наступним чином:
Таким чином встановлюється обмеження для SQLite. Тепер спроби вставити рядок в таблицю «track», для якої немає відповідного рядка в таблиці «artist», будуть неуспішні, так само як і видалення рядка з таблиці «artist», що має залежні рядки в таблиці «track».
Але є один виняток: якщо стовпець зовнішнього ключа в таблиці «track» має значення NULL, то існування відповідної йому рядки в таблиці «artist» не потрібно. У виразах SQL це означає, що для кожного рядка в таблиці «track» наступні вираження повинно бути істинним:
Порада: якщо додаток вимагає суворого відповідності між «artist» і «track», то значення NULL слід заборонити в стовпці «trackartist». Це можна зробити просто додавши в схему відповідне обмеження NOT NULL.
Наступний висновок сеансу SQLite в режимі командного рядка ілюструє результат дії зовнішнього ключа, доданого до таблиці «track»:
Далі ви можете переконатися, що неможливо так маніпулювати базою даних, видаляти і змінювати рядки таблиці «artist», щоб порушувалося обмеження зовнішнього ключа:
У SQLite використовується наступна термінологія:
Обмеження зовнішнього ключа є задоволеним, якщо для кожного рядка дочірньої таблиці виконано одну з двох умов: один або більше стовпців дочірнього ключа мають значення NULL, або, якщо існує такий рядок в батьківській таблиці, що кожен стовпець батьківського ключа має значення, еквівалентне значенню відповідного стовпця в дочірньому ключі.
Термін «еквівалентну» в попередньому абзаці використовується в тому сенсі, що порівняння здійснюється із застосуванням правил, описаних тут. У поточному контексті ці правила означають наступне:
- Коли порівнюються текстові значення, завжди використовується сортує послідовність. асоційована зі стовпцем батьківського ключа.
- Коли порівнюються значення і батьківський ключ є афіновані. то перед порівнянням ця аффинирования застосовується також і до значення дочірнього ключа.
2. Включення підтримки зовнішніх ключів