Ранее мы создавали идентификаторы вручную. Так можно делать в учебных целях, но в реальной жизни эту задачу берут на себя СУБД. За это отвечает механизм автогенерации. В этом уроке мы рассмотрим основной принцип этого механизма, а также разберем, что такое первичный ключ и как он помогает идентифицировать строки.
Первичный ключ
Первичный ключ однозначно идентифицирует каждую запись внутри таблицы. Задается с помощью фразы PRIMARY KEY, которая добавляется после указания типа, когда создается таблица. Первичный ключ в таблице может быть только один, и для него используется отдельное поле — идентификатор, у которого нет физического смысла:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(255),
first_name VARCHAR(50),
last_name VARCHAR(50),
created_at TIMESTAMP
);
-- Наполняем таблицу данными
INSERT INTO users (id, created_at, email, first_name, last_name, username) VALUES (1, '2022-06-14 18:31:05.296', 'Trevion53@yahoo.com', 'Lucienne', 'Feil', 'Duncan3');
INSERT INTO users (id, created_at, email, first_name, last_name, username) VALUES (2, '2022-06-14 02:04:13.104', 'Baylee52@yahoo.com', 'Ramiro', 'Wolf', 'Michaela11');https://www.db-fiddle.com/f/7Zome1rgjV5y8bjx63rf4/0
Если вставить новую запись со значением, которое уже есть в колонке с первичным ключом, то запрос не выполнится.
-- Query Error: error: duplicate key value violates unique constraint "users_pkey"
-- id = 1 уже существует в таблице
INSERT INTO users (id, created_at, email, first_name, last_name, username) VALUES (1, '2022-06-14 02:04:13.104', 'Casimer_Cronin@yahoo.com', 'Maureen', 'Romaguera', 'Roma5');Автогенерация первичного ключа
Первичный ключ в базах данных принято заполнять автоматически, используя встроенные в базу данных возможности. Такой подход лучше ручного заполнения по двум причинам. Во-первых, это просто реализовать. Во-вторых, база данных сама следит за уникальностью во время генерации.
Автогенерация работает по следующим принципам:
-
Внутри базы создается отдельный счетчик, который привязывается к каждой таблице.
-
Счетчик увеличивается на единицу при вставке новой строки.
-
Получившееся значение записывается в поле, которое помечается как автогенерируемое.
Автогенерацию первичного ключа часто называют автоинкрементом (autoincrement). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования ++.
До определенного момента механизм автоинкремента был реализован по-своему в каждой СУБД разными способами. Это создавало проблемы при переходе от одной СУБД к другой и усложняло реализацию программного слоя доступа к базе данных.
Такой автоинкремент известен под именем GENERATED AS IDENTITY:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY,
username VARCHAR(50),
email VARCHAR(255),
first_name VARCHAR(50),
last_name VARCHAR(50),
created_at TIMESTAMP
);Основной ключ и автоинкремент часто используются вместе:
CREATE TABLE users (
-- Одновременное использование и первичного ключа и автогенерации
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
username VARCHAR(50),
email VARCHAR(255),
first_name VARCHAR(50),
last_name VARCHAR(50),
created_at TIMESTAMP
);
INSERT INTO users (created_at, email, first_name, last_name, username) VALUES ('2022-06-14 18:31:05.296', 'Trevion53@yahoo.com', 'Lucienne', 'Feil', 'Duncan3');
INSERT INTO users (created_at, email, first_name, last_name, username) VALUES ('2022-06-14 02:04:13.104', 'Baylee52@yahoo.com', 'Ramiro', 'Wolf', 'Michaela11');
INSERT INTO users (created_at, email, first_name, last_name, username) VALUES ('2022-06-14 02:28:26.058', 'Casimer_Cronin@yahoo.com', 'Maureen', 'Romaguera', 'Margarete_Hegmann6');https://www.db-fiddle.com/f/aabHfxoXhBwFJy4FhqyRsc/0
| id | username | first_name | last_name | created_at | |
|---|---|---|---|---|---|
1 |
Duncan3 |
Trevion53@yahoo.com |
Lucienne |
Feil |
2022-06-14T18:31:05.296Z |
2 |
Michaela11 |
Baylee52@yahoo.com |
Ramiro |
Wolf |
2022-06-14T02:04:13.104Z |
3 |
Margarete_Hegmann6 |
Casimer_Cronin@yahoo.com |
Maureen |
Romaguera |
2022-06-14T02:28:26.058Z |
Если удалить запись с id равным двум и вставить еще одну запись, то значением поля id будет 4.
DELETE FROM users WHERE id = 2;
INSERT INTO users (created_at, email, first_name, last_name, username) VALUES ('2022-06-13 22:36:55.322', 'Angelita.Altenwerth96@hotmail.com', 'Jennyfer', 'Flatley', 'Kayley.Turcotte98');
SELECT * FROM users;https://www.db-fiddle.com/f/mLbmUrYy9AMKjbFYawkBPB/1
| id | username | first_name | last_name | created_at | |
|---|---|---|---|---|---|
1 |
Duncan3 |
Trevion53@yahoo.com |
Lucienne |
Feil |
2022-06-14T18:31:05.296Z |
3 |
Margarete_Hegmann6 |
Casimer_Cronin@yahoo.com |
Maureen |
Romaguera |
2022-06-14T02:28:26.058Z |
4 |
Kayley.Turcotte98 |
Angelita.Altenwerth96@hotmail.com |
Jennyfer |
Flatley |
2022-06-13T22:36:55.322Z |
Автогенерация не связана с данными в таблице. Это отдельный счетчик, который всегда увеличивается. Так избегаются вероятные коллизии и ошибки, когда один и тот же идентификатор принадлежит сначала одной записи, а потом другой.
Вот его сигнатура из документации:
column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]-
Тип данных может быть SMALLINT, INT или BIGINT
-
GENERATED ALWAYS— не позволит добавлять значение самостоятельно, используяUPDATEилиINSERT -
GENERATED BY DEFAULT— в отличие от предыдущего варианта, этот вариант позволяет добавлять значения самостоятельно
PostgreSQL позволяет иметь более одного автогенерируемого поля на таблицу.
Выводы
В этом уроке мы узнали про первичные ключи. Использование первичных ключей в SQL помогает обеспечить целостность данных в базе. Первичные ключи помогают уникально идентифицировать записи в таблице.
Механизм автоинкремента позволяет автоматически увеличивать значения ключей при каждой новой записи, что делает его простым в использовании и предотвращает возможные конфликты и ошибки.
Дополнительные материалы
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
