Логотип Хекслета

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');

View on DBFiddle

Если вставить новую запись со значением, которое уже есть в колонке с первичным ключом, то запрос не выполнится.

-- 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). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования {plus}{plus}.

Такой автоинкремент создается с помощью GENERATED ALWAYS 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');

View On DBFiddle

idusernameemailfirst_namelast_namecreated_at
1Duncan3Trevion53@yahoo.comLucienneFeil2022-06-14T18:31
.296Z
2Michaela11Baylee52@yahoo.comRamiroWolf2022-06-14T02:04
.104Z
3Margarete_Hegmann6Casimer_Cronin@yahoo.comMaureenRomaguera2022-06-14T02:28
.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;

View On DBFiddle

idusernameemailfirst_namelast_namecreated_at
1Duncan3Trevion53@yahoo.comLucienneFeil2022-06-14T18:31
.296Z
3Margarete_Hegmann6Casimer_Cronin@yahoo.comMaureenRomaguera2022-06-14T02:28
.058Z
4Kayley.Turcotte98AngelitaAngelita.Altenwerth96@hotmail.comJennyferFlatley2022-06-13T22:36
.322Z

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

Вот его сигнатура из документации:

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
  • Тип данных может быть SMALLINT, INT или BIGINT
  • GENERATED ALWAYS -- не позволит добавлять значение самостоятельно, используя UPDATE или INSERT
  • GENERATED BY DEFAULT -- в отличие от предыдущего варианта, этот вариант позволяет добавлять значения самостоятельно

PostgreSQL позволяет иметь более одного автогенерируемого поля на таблицу.

Ниже пример создания таблицы с помощью GENERATED BY DEFAULT AS IDENTITY

CREATE TABLE users (
    -- Одновременное использование и первичного ключа и автогенерации
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    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');
INSERT INTO users (id, created_at, email, first_name, last_name, username) VALUES (3, '2022-06-14 02:28:26.058', 'Casimer_Cronin@yahoo.com', 'Maureen', 'Romaguera', 'Margarete_Hegmann6');

View On DBFiddle

idusernameemailfirst_namelast_namecreated_at
1Duncan3Trevion53@yahoo.comLucienneFeil2022-06-14 18:31
.296
2Michaela11Baylee52@yahoo.comRamiroWolf2022-06-14 02:04
.104
3Margarete_Hegmann6Casimer_Cronin@yahoo.comMaureenRomaguera2022-06-14 02:28
.058

Выводы

В этом уроке мы узнали про первичные ключи. Использование первичных ключей в SQL помогает обеспечить целостность данных в базе. Первичные ключи помогают уникально идентифицировать записи в таблице.

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

Рекомендуемые программы

Навигация
Теория
Тесты
Упражнение