Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Первичный ключ и автоинкремент Основы SQL

Ранее мы создавали идентификаторы вручную. Так можно делать в учебных целях, но в реальной жизни эту задачу берут на себя СУБД. За это отвечает механизм автогенерации. В этом уроке мы рассмотрим основной принцип этого механизма, а также разберем, что такое первичный ключ и как он помогает идентифицировать строки.

Первичный ключ

Первичный ключ однозначно идентифицирует каждую запись внутри таблицы. Задается с помощью фразы 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');

Автогенерация первичного ключа

Первичный ключ в базах данных принято заполнять автоматически, используя встроенные в базу данных возможности. Такой подход лучше ручного заполнения по двум причинам. Во-первых, это просто реализовать. Во-вторых, база данных сама следит за уникальностью во время генерации.

Автогенерация работает по следующим принципам:

  1. Внутри базы создается отдельный счетчик, который привязывается к каждой таблице.

  2. Счетчик увеличивается на единицу при вставке новой строки.

  3. Получившееся значение записывается в поле, которое помечается как автогенерируемое.

Автогенерацию первичного ключа часто называют автоинкрементом (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 email 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 email 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 помогает обеспечить целостность данных в базе. Первичные ключи помогают уникально идентифицировать записи в таблице.

Механизм автоинкремента позволяет автоматически увеличивать значения ключей при каждой новой записи, что делает его простым в использовании и предотвращает возможные конфликты и ошибки.


Дополнительные материалы

  1. Документация PRIMARY KEY
  2. Документация GENERATED AS IDENTITY

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
Обучитесь разработке бэкенда сайтов и веб-приложений — серверной части, которая отвечает за логику и базы данных
10 месяцев
с нуля
Старт 21 ноября
профессия
Выполняйте ручное тестирование веб-приложений, находите ошибки в продукте. Узнайте все о тест-дизайне.
4 месяца
с нуля
Старт 21 ноября
профессия
Научитесь разработке веб-приложений, сайтов и программного обеспечения на языке Java, программируйте и используйте структуры данных
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Собирайте, анализируйте и интерпретируйте данные, улучшайте бизнес-процессы и продукт компании. Обучитесь работе с библиотеками Python
9 месяцев
с нуля
Старт 21 ноября
профессия
Занимайтесь созданием сайтов, веб-приложений, сервисов и их интеграцией с внутренними бизнес-системами на бекенд-языке PHP
10 месяцев
с нуля
Старт 21 ноября
профессия
Обучитесь разработке визуальной части сайта — фронтенда, а также реализации серверной — бэкенда. Освойте HTML, CSS, JavaScript
16 месяцев
с нуля
Старт 21 ноября
профессия
Разработка бэкенд-компонентов для веб-приложений
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Организовывайте процесс автоматизации тестирования на проекте, обучитесь языку программирования JavaScript, начните управлять процессом тестирования
8 месяцев
c опытом
Старт 21 ноября

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»