База SQLite и основы SQL. Часть 4 (ключи)
11-11-2021Время чтения ~ 8 мин.SQL 3138
Обычно в базе находятся несколько таблиц. Поэтому нужно понимать как их правильно организовать. Например в нашей таблице firstTable мы уже столкнулись с проблемой отсутствия уникального номера записи. И на самом деле в таблице есть ещё одна проблема — возможность создания дублей (когда было два Пети с одним номером).
Поэтому при проектировании базы нужно учитывать сразу несколько моментов: структура таблиц, ограничение на ввод данных и организация связей между таблицами. Нюанс здесь в том, что данные мы можем вносить, только после создания таблиц, а значит нужно на самом раннем этапе понимать как правильно это сделать.
В процессе использования баз, были разработаны подходы, которые называются нормализацией. Когда база удовлетворяет определенным критериям, то говорят, что она удовлетворяет нормальной форме (НФ). Существует 6 уровней нормальных форм, где каждый уровень повышает требование к базе. На практике же используется только 2 и 3-я форма (2НФ, 3НФ). Я не буду давать всю теорию по нормализации, поскольку она достаточно скучна, поэтому просто покажу разные примеры по нормализации.
Первичный ключ
Пример с Петей, когда мы пытались его обновить, показывает важность того, чтобы каждая запись имела свой уникальный номер (ID). В рамках SQL говорят об (первичных) ключах (PRIMARY KEY) и индексах (INDEX).
Про индексы поговорим позже.
Ключ — это уникальное поле таблицы. Например, если мы укажем поле id как PRIMARY KEY, то это позволит избежать в таблице дублей. В таблице может быть только одно такое поле, которое задаётся в момент создания таблицы.
CREATE TABLE my1( id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO my1 VALUES(1, 'один'); INSERT INTO my1 VALUES(2, 'два'); INSERT INTO my1 VALUES(1, 'еще один'); -- ошибка! «UNIQUE constraint failed: my1.id»
Поле id в этом примере мы задали как первичный ключ. Когда мы попытаемся добавить уже существующее значение, SQLite выдаст ошибку и добавления не произойдёт. Конструкция INTEGER PRIMARY KEY в SQLite имеет ещё одну особенность — ключ становится автоинкрементным, то есть будет обладать уникальностью и автоматически увеличиваться, даже если его не указывать. Например:
INSERT INTO my1 (name) VALUES ('три');
SELECT * FROM my1;
id name
--------
1 один
2 два
3 три
То есть мы опустили добавление поля id, но при этом оно автоматом было добавлено.
На практике немного удобней использовать «чистый» автоинкремент. Это ключевое слово AUTOINCREMENT. Удалим таблицу и создадим её заново в таком виде.
DROP TABLE my1; -- удалили таблицу
-- создадим заново
CREATE TABLE my1(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
-- добавим записи
INSERT INTO my1 (name) VALUES ('один');
INSERT INTO my1 (name) VALUES ('два');
INSERT INTO my1 (name) VALUES ('три');
SELECT * FROM my1;
id name
--------
1 один
2 два
3 три
Теперь удалим третью запись и заново её добавим:
DELETE FROM my1 WHERE id = 3;
INSERT INTO my1 (name) VALUES ('три');
SELECT * FROM my1;
id name
--------
1 один
2 два
4 три
Обратите внимание, что у новой записи изменился id — он стал не 3, а 4. Такое поле будет обладать не только уникальностью в пределах текущих данных, но и в пределах всей жизни таблицы. Это позволяет избежать ситуаций, когда запись была удалена, но к ней продолжают происходить обращения. С автоинкрементным ключом мы всегда уверены в уникальность каждой записи, даже если её больше нет.
Если говорить совсем строго то полный код первичного ключа автоинкремента будет таким:
CREATE TABLE my1( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT );
Конструкция NOT NULL указывает, что поле не может быть пустым. В стандарте SQL первичный ключ не может быть пустым по определению, но в SQLite — может (поскольку NULL сам по себе уникален). Это просто особенность СУБД.
Уникальность
Для того, чтобы сделать поле уникальным, используется ключевое слово UNIQUE.
CREATE TABLE my1(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT UNIQUE
);
-- добавим записи
INSERT INTO my1 (name) VALUES ('один');
INSERT INTO my1 (name) VALUES ('два');
INSERT INTO my1 (name) VALUES ('один'); -- ошибка «UNIQUE constraint failed: my1.name»
С помощью UNIQUE можно контролировать уникальность данных в базе. Например если в базе список фамилий, то можно сделать так, чтобы они не повторялись.
В некоторых случаях уникальность нужно добавить не к одному полю, а к связке нескольких полей. Например есть таблица с именами и фамилиями. Понятно, что по отдельности они могут повторяться, но комбинация фамилия+имя должны быть уникальными. Для этого используется немного другая конструкция UNIQUE при создании таблицы.
CREATE TABLE my1(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name1 TEXT,
name2 TEXT,
UNIQUE(name1, name2)
);
INSERT INTO my1 (name1, name2) VALUES ('Вася', 'Иванов');
INSERT INTO my1 (name1, name2) VALUES ('Иванов', 'Вася');
SELECT * FROM my1;
name1 name2
------------
Вася Иванов
Иванов Вася
Теперь, если мы попробуем добавить ещё одного «Вася Иванов», база вернёт ошибку.
INSERT INTO my1 (name1, name2) VALUES ('Вася', 'Иванов');
-- ошибка UNIQUE constraint failed: my1.name1, my1.name2
Таким образом, если стоит задача обеспечить уникальность полей, используется UNIQUE.
Осторожней с уникальностью
Заманчиво, конечно, использовать UNIQUE для данных, но здесь легко попасть в логическую ловушку. Например полных однофамильцев (даже с учётом дня рождения) полно. Или ещё один пример из реальной жизни.
Есть база данных, где номер телефона — уникальный. Это логично — номера не повторяются. Чтобы подтвердить принадлежность номера, на него отправляется СМС. Однако возникла ситуация, когда два клиента перепутали номера между собой (муж и жена). То есть нужно просто поменять эти два номера между собой.
Когда мы меняем номер жены, то указываем номер мужа, но поскольку он уже есть в системе, то сайт выдаёт ошибку «Номер уже используется». Получается замкнутый круг. Таким образом, чтобы изменить телефоны необходимо либо ручное редактирование базы, либо придумывать обходные пути, например помечать поле телефона как NULL, после чего можно его поменять. При этом, если произойдёт потеря авторизации, то клиент больше не сможет войти в свой аккаунт, поскольку номер телефона уже не указан.
Именно по этой причине, многие предпочитают не перекладывать проверку уникальности на базу данных. В качестве уникального ID используется автоинкремент, а остальные поля могут повторяться. Когда происходит обновление данных (например номер телефона), то можно выполнить дополнительный запрос и убедиться, что телефон уже используется. И дальше предложить смену номера при условии, что рядом есть новый и старый телефоны. Проблем со сменой номера уже не будет.
Атомарность
В основном база нужна для того, чтобы формировать разные выборки. Если поле содержит разнородное содержимое, то будет сложно сделать корректную выборку. Рассмотрим такую базу.
-- создадим таблицу
CREATE TABLE my2(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT,
skill TEXT
);
-- добавим данные
INSERT INTO my2 (name, skill) VALUES ('Вася', 'php, js, sql');
INSERT INTO my2 (name, skill) VALUES ('Петя', 'js, sql, pascal');
INSERT INTO my2 (name, skill) VALUES ('Миша', 'react, js, php');
-- посмотрим
SELECT * FROM my2;
id name skill
-----------------
1 Вася php, js, sql
2 Петя js, sql, pascal
3 Миша react, js, php
Это список людей и их навыков. Если мы хотим найти тех, кто умеет работать с SQL, необходимо задать условие по полю skill. Но данные в этом поле разнородные. В лучшем случае можно воспользоваться LIKE, чтобы найти вхождение. А если навыки указаны в разном регистре или в другом виде? Ну и как минимум нужно заранее знать какие вообще могут быть навыки. Если записей в таблице будет сотня, то просмотр её займёт много времени.
Когда возникает такая ситуация, то говорят о том, что база не нормализирована. Когда мы рассмотрели уникальность, то это тоже один из первых шагов к нормальной форме. Сейчас нужно сделать так, чтобы поле skill стало атомарным, то есть содержало только одно атомарное значение.
И здесь мы опять же возвращаемся к вопросу проектирования базы данных. Есть разные подходы. Самый простой — это добавить в таблицу поля, рассчитанные под точную задачу. Например ввести поля php, js и т.д., где отметка 1, будет означать, что навык есть.
-- создание таблицы
CREATE TABLE my3(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT,
php INTEGER DEFAULT 0,
js INTEGER DEFAULT 0,
sql INTEGER DEFAULT 0,
pascal INTEGER DEFAULT 0
);
-- вставим данные
INSERT INTO my3 (name, php, js, sql) VALUES ('Вася', 1, 1, 1);
INSERT INTO my3 (name, js, sql, pascal) VALUES ('Петя', 1, 1, 1);
-- посмотрим, что получилось
SELECT * FROM my3;
id name php js sql pascal
-------------------------------
1 Вася 1 1 1 0
2 Петя 0 1 1 1
Обратите внимание, что в запросе создания таблицы я использую ключевое слово DEFAULT, которое задаёт значение по умолчанию. Именно поэтому в INSERT мы указываем только те поля, которые следует отметить. Остальные получат дефолтное значение.
Чтобы узнать пользователей, которые имеют навык «js» нужно будет выполнить такой запрос:
SELECT name FROM my3 WHERE js = 1; name ---- Вася Петя
Если навыков будет немного и они фиксированы, то такой подход имеет право на существование. Но что делать, если потребуется добавить новый навык? Создавать новую колонку — не самое лучшее решение.
Чтобы решить такую задачу, используют две (хотя бы) таблицы. Первая, содержит список навыков, а вторая ссылается на первую.
Вначале создадим таблицу навыков.
CREATE TABLE skills (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT UNIQUE
);
INSERT INTO skills (name) VALUES ('php');
INSERT INTO skills (name) VALUES ('js');
INSERT INTO skills (name) VALUES ('sql');
INSERT INTO skills (name) VALUES ('pascal');
SELECT * FROM skills;
id name
---------
1 php
2 js
3 sql
4 pascal
Теперь создадим таблицу юзеров:
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user TEXT, skill_id INTEGER DEFAULT NULL );
Обратите внимание на поле skill_id — оно будет хранить номер из таблицы skills. Например навык «js» имеет номер 2. Таким образом мы можем хранить в этой таблице список пользователей и их многочисленные навыки.
INSERT INTO users (user, skill_id) VALUES ('Вася', 1);
INSERT INTO users (user, skill_id) VALUES ('Вася', 2);
INSERT INTO users (user, skill_id) VALUES ('Вася', 3);
INSERT INTO users (user, skill_id) VALUES ('Петя', 2);
INSERT INTO users (user, skill_id) VALUES ('Петя', 3);
INSERT INTO users (user, skill_id) VALUES ('Петя', 4);
SELECT * FROM users;
id name skill_id
------------------
1 Вася 1
2 Вася 2
3 Вася 3
4 Петя 2
5 Петя 3
6 Петя 4
Теперь, если нам нужно получить всех, кто знает js, мы смотрим номер навыка и формируем запрос:
SELECT user FROM users WHERE skill_id = 2; user ---- Вася Петя
Такая нормализация позволяет удобно управлять базой. Если нужно добавить новый навык, то это легко делается в таблице skills. Точно также можно редактировать таблицу users, задавая разные навыки пользователям.
Таблица skills в такой базе называется справочной. Её задача — хранить возможные варианты. Например вы предлагаете на сайте выбрать одно значение из выпадающего списка. Набор значений можно будет взять из справочной таблицы.
В заключении этой части небольшое замечание по нормализации. Легко заметить, что таблица users содержит дубли в поле user. Нетрудно догадаться, что нормальная форма будет получена если мы выделим всех пользователей в отдельную справочную таблицу, а в итоговой будем хранить номер пользователя и номер навыка. То есть по хорошему должно быть три таблицы. В следующих частях я покажу как это делается (достигается 3НФ).