Как сделать автозаполнение id в sql

Автоувеличение id в PostgreSQL

Дополню ответ @John Brown, так как типы SERIAL и BIGSERIAL появились в Postgres не так давно.

Еще один способ создать автоинкрементное поле заключается в том, чтобы связать его с последовательностью (SEQUENCE), наподобие того, как это делается в Оракле:

CREATE SEQUENCE table_id_seq; ALTER TABLE table ALTER COLUMN id SET DEFAULT NEXTVAL('table_id_seq'); 

Отслеживать
ответ дан 15 сен 2011 в 16:56
2,140 10 10 серебряных знаков 10 10 бронзовых знаков

Начиная с PostgreSQL 10 был реализован синтаксис создания автоматически генерируемых последовательностей соответствующий современным стандартам SQL.

сreate table foo ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ); 

Этот синтаксис является стандартом, и такой запрос будет переносим между, например, PostgreSQL, DB2, Oracle.

Если чуть углубиться внутрь — этот синтаксис postgresql опирается на уже давно существующие свои sequence . Так, этим запросом был неявно создан новый sequence и дополнительно привязан к полю таблицы, так чтобы его не получилось случайно удалить.

Упомянутые в других ответах smallserial , serial и bigserial — это тоже ненастоящие типы данных и как таковых их не существует. Например, вы не сможете привести другой тип данных к serial. Это простой синтаксический сахар вокруг создания sequence и назначения default для поля. Так, таблица вида

create table foo (id serial); 

CREATE TABLE public.foo ( id integer NOT NULL ); CREATE SEQUENCE public.foo_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE public.foo_id_seq OWNED BY public.foo.id; ALTER TABLE ONLY public.foo ALTER COLUMN id SET DEFAULT nextval('public.foo_id_seq'::regclass); 

И именно так его видно через pg_dump , например.

Поэтому фактически ответ: если вы хотите автоматически генерируемый счётчик в postgresql, безопасный для конкурентного доступа — вам нужны sequence . А способов его объявить, как это часто бывает в SQL, есть несколько разных.

Если версия базы позволяет, наверное будет лучше уже использовать стандартный SQL синтаксис GENERATED BY DEFAULT AS IDENTITY .

Использование автоматической нумерации в СУБД Oracle

В данном посте описываются способы добавления функционала автоматической нумерации к таблице в базах данных Oracle.

Автоматическая нумерация или автоинкремент (в некоторых системах управления базами данных, например, в MySQL, для автоматической нумерации используется понятие автоинкремент) – это автоматическое увеличение значения колонки в таблице, чаще всего колонки, содержащей значения первичных ключей – Primary Key (обязательны к заполнению и должны быть уникальны). Можно прописывать эти значения вручную, но в промышленной среде, когда в таблицы записывается много данных, запоминать предыдущее значение и сохранять уникальность значений трудно, что скажется на скорости заполнения таблиц. Для промышленной среды такая ситуация недопустима, и в целом – очень неудобна.

Ручная нумерация

Рассматривается пример с ручным вводом данных для значений колонки с primary key.

Шаг 1. Создается таблица test с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

create table test ( id number primary key, city_name varchar2(40) not null); Table TEST created.

Шаг 2. Добавляются данные в таблицу test.

insert into test values ('1', 'Dushanbe'); 1 row inserted. insert into test values ('2', 'Moscow'); 1 row inserted.

Данные (2 строки) успешно добавлены. Далее будет выполнена попытка добавления третьей строки со значением NULL и без указания данных для колонки id.

Добавление третьей строки с указанием NULL:

insert into test values (NULL, 'Tashkent');

и добавление строки без указания данных для колонки id:

insert into test (city_name) values ('Tashkent');

и в обоих случаях система выдаст следующую ошибку:

Error report - ORA-01400: cannot insert NULL into ("HR"."TEST"."ID")

Данная ошибка (ORA-01400) возникает из-за того, что новая добавляемая строка не содержит значение для колонки id. Колонка id таблицы test является первичным ключом (primary key) и требуется обязательное указание уникального значения.

Если добавить следующую строку,

insert into test values ('2','Moscow');

то система выдаст ошибку:

Error report - ORA-00001: unique constraint (HR.SYS_C0012160) violated

Данная ошибка (ORA-00001) возникает из-за того, что нарушено условие уникальности значений в колонке id. В таблице уже есть строка со значением 2 в колонке id. Данный тип ошибки возникает при попытке выполнить команды INSERT или UPDATE, которые пытаются вставить дублирующее значение в колонку с ограничением по уникальности значений.

Шаг 3. Проверка данных в таблице.

В таблице test после всех вышеперечисленных операций будут следующие 2 строки данных:

select * from test; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 

Автоматическая нумерация.

Общий принцип механизма автоматической нумерации можно описать следующим образом: внутри базы к каждой таблице создается и привязывается отдельный счетчик, который увеличивается на единицу при вставке новой строки, а получившееся значение записывается в ту колонку, которое помечено как автонумерация (автоинкремент).

У различных СУБД этот механизм реализован по-своему. В данном посте рассматривается применение данного механизма применительно к СУБД Oracle.

В Oracle автонумерацию можно реализовать несколькими способами. Например, с помощью:

1. Последовательности (Sequence),
2. Комбинации последовательности и триггера (Trigger),
3. Использования колонки с IDENTITY.

Ниже рассматривается каждый способ с примерами.

1. Автоматическая нумерация с помощью последовательности (Sequence).

Для баз данных, использующих Oracle Database версии до 12c одним из способов реализации является sequence (последовательность). Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда нужно создать уникальный номер в качестве значения первичного ключа.

Шаг 1. Создается таблица test_sequence с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

create table test_sequence ( id number primary key, city_name varchar2(40) not null); Table TEST_SEQUENCE created.

Шаг 2. Ниже создается последовательность с названием t_sequence. Значение данной последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

create sequence t_sequence start with 1 increment by 1; Sequence T_SEQUENCE created.

Последовательность создана. Синтаксис создания последовательности позволяет, кроме start with и increment by, задать также и другие параметры. Например, maxvalue, minvalue, cycle, cache и т.д. Но в данном посте для упрощения задачи они опускаются.

Шаг 3. Добавляются данные в таблицу test_sequence. Вместо значения id прописывается параметр nextval последовательности t_sequence (t_sequence.nextval).

insert into test_sequence values(t_sequence.nextval,'Dushanbe'); 1 row inserted. insert into test_sequence values(t_sequence.nextval,'Moscow'); 1 row inserted. insert into test_sequence values(t_sequence.nextval,'Tashkent'); 1 row inserted.

Все данные успешно добавлены.

Шаг 4. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3, то есть значения уникальны и параметр nextval последовательности вставил их вместо ручного ввода пользователем.

select * from test_sequence; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 3 Tashkent 

2. Автоматическая нумерация с помощью комбинации последовательности и тригерра.

Данный способ можно применить к любой версии Oracle. Также как и в предыдущем способе, используется последовательность. Однако, в данном способе последовательность применяется в комбинации с другим объектом Oracle – триггером.

Шаг 1. Создается таблица test_sequence_trigger с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

create table test_sequence_trigger ( id number primary key, city_name varchar2(40) not null); Table TEST_SEQUENCE_TRIGGER created.

Шаг 2. Ниже создается последовательность с названием tt_sequence. Значение последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

create sequence tt_sequence start with 1 increment by 1; Sequence TT_SEQUENCE created.

Шаг 3. Создается триггер tt_trigger для таблицы test_sequence_trigger. Каждый раз, перед тем как данные будут добавляться во время выполнения оператора INSERT в таблицу test_sequence_trigger, триггер с именем tt_trigger будет запрашивать число из созданной последовательности tt_sequence и подставлять его в колонку id.

create trigger tt_trigger before insert on test_sequence_trigger for each row begin select tt_sequence.nextval into :new.id from dual; end; Trigger TT_TRIGGER compiled

Шаг 4. Выполняется вставка данных в таблицу test_sequence_trigger.

insert into test_sequence_trigger (city_name) values ('Dushanbe'); 1 row inserted. insert into test_sequence_trigger (city_name) values ('Moscow'); 1 row inserted. insert into test_sequence_trigger (city_name) values ('Tashkent'); 1 row inserted.

Шаг 5. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

select * from test_sequence_trigger; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 3 Tashkent 

Применение комбинации последовательности и триггера, позволило строкам автоматически получить уникальные числовые значения в колонке id.

3. Автоматическая нумерация с помощью использования колонки с IDENTITY.

Начиная с версии Oracle Database 12c есть возможность создать колонку с IDENTITY для автоматической нумерации.

Колонку с типом IDENTITY можно объявить при создании таблицы, либо изменить существующую таблицу, добавив в неё колонку с типом IDENTITY используя DDL команду ALTER TABLE.

Общий синтаксис создания таблицы с колонкой IDENTITY приведен ниже:

GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY [sequence_options,…]

Колонку IDENTITY можно создать с одной из трех опций генерации значений:

• GENERATED BY DEFAULT AS IDENTITY – Генератор последовательностей добавляет значение в колонку IDENTITY каждый раз, когда значение не предоставлено вручную.
• GENERATED BY DEFAULT ON NULL AS IDENTITY – Генератор последовательностей добавляет следующее значение колонки IDENTITY, если будет вручную указано значение NULL для колонки.
• GENERATED ALWAYS AS IDENTITY – Генератор последовательностей всегда добавляет значение в колонку IDENTITY. Не предоставляется возможности вручную указать значение.

У каждой колонки с IDENTITY есть генератор последовательности – Sequence Generator. У данного генератора есть различные атрибуты, которые по умолчанию применяют определенные значения. Но при необходимости можно самому задать эти значения. Ниже приведены некоторые из атрибутов генератора последовательности. В разделе 3.3.3 они применены при создании новой таблицы.

• START WITH initial_value – эта опция контролирует первое (начальное) значение (число) для колонки IDENTITY. По умолчанию, это значение равно 1.
• INCREMENT BY internval_value – эта опция определяет с каким шагом будут генерироваться числа последовательности. По умолчанию, это значение равно 1.

Ниже в примерах описывается применение IDENTITY с различными опциями.

3.1. Опция GENERATED BY DEFAULT AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT AS IDENTITY для колонки id.

create table test_identity ( id number generated by default as identity, city_name varchar2(40) not null); Table TEST_IDENTITY created.

Шаг 2. Добавление данных в таблицу test_identity.

insert into test_identity (city_name) values ('Dushanbe'); 1 row inserted. insert into test_identity (city_name) values ('Moscow'); 1 row inserted. insert into test_identity values (3,'Tashkent') 1 row inserted.

Данные успешно добавлены.

Если попытаться вставить в колонку с типом IDENTITY значение NULL:

insert into test_identity values (NULL,'Tashkent');

то система выдаст следующую ошибку:

ORA-01400: cannot insert NULL into ("HR"."TEST_IDENTITY"."ID")

Опция GENERATED BY DEFAULT AS IDENTITY не позволяет вставлять значения типа NULL в колонку с типом IDENTITY. Если значение для колонки не указано, то генератор последовательностей (Sequence Generator) будет использовать свое значение.

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

select * from test_identity; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 3 Tashkent 

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.2).

drop table test_identity; Table TEST_IDENTITY dropped.

3.2. Опция GENERATED BY DEFAULT ON NULL AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT ON NULL AS IDENTITY для колонки id.

create table test_identity ( id number generated by default on null as identity, city_name varchar2(40) not null); Table TEST_IDENTITY created.

Шаг 2. Добавление данных в таблицу test_identity.

insert into test_identity (city_name) values ('Dushanbe'); 1 row inserted. insert into test_identity (city_name) values ('Moscow'); 1 row inserted. insert into test_identity values (NULL,'Tashkent'); 1 row inserted. insert into test_identity values (NULL,'Astana'); 1 row inserted. insert into test_identity (city_name) values ('Bishkek'); 1 row inserted.

Данные успешно добавлены.

Выше были вставлены значения NULL в колонку с типом IDENTITY и не было ошибки (в отличие от опции пункта 3.1). Опция GENERATED BY DEFAULT ON NULL AS IDENTITY позволяет вставлять значения типа NULL в колонку IDENTITY.

Шаг 3. Проверяется содержимое таблицы.

select * from test_identity; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 3 Tashkent 4 Astana 5 Bishkek 

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.3).

drop table test_identity; Table TEST_IDENTITY dropped.

3.3. Опция GENERATED ALWAYS AS IDENTITY.

Шаг 1. Создание таблицы с опцией GENERATED ALWAYS AS IDENTITY для колонки id.

create table test_identity ( id number generated always as identity, city_name varchar2(40) not null); Table TEST_IDENTITY created.

Шаг 2. Добавление данных в таблицу test_identity.

insert into test_identity (city_name) values ('Dushanbe'); 1 row inserted. insert into test_identity (city_name) values ('Moscow'); 1 row inserted.

Данные успешно добавлены. В данном случае, если попытаться вставить в колонку с типом IDENTITY любое значение, то появится сообщение об ошибке. Опция GENERATED ALWAYS AS IDENTITY не позволяет вручную вставлять значения колонку IDENTITY.

insert into test_identity values (3,'Tashkent') ORA-32795: cannot insert into a generated always identity column

insert into test_identity values (NULL,'Tashkent') ORA-32795: cannot insert into a generated always identity column

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонке id автоматически вставились значения для двух успешно добавленных строк.

select * from test_identity; ID CITY_NAME ----- ---------- 1 Dushanbe 2 Moscow 

Далее выполняется ввод данных из двух сессий без выполнения команды commit для проверки принципа работы генератора последовательности с опцией GENERATED ALWAYS AS IDENTITY.

3.3.1 Первая сессия:

Выполняется вставка новой строки:

insert into test_identity (city_name) values ('Tashkent');

Строка добавлена. Выполняется выборка данных из таблицы:

select * from test_identity; ID CITY_NAME ----- ---------- 3 Tashkent 1 Dushanbe 2 Moscow 

Как видно, пользователь первой сессии после вставки новой строки видит 3 строки данных.

3.3.2. Ввод и выборка данных второй сессии:

Выполняется вставка новой строки второй сессии:

insert into test_identity (city_name) values ('Astana');

Строка успешно добавлена. Выполняется выборка данных второй сессии. Пользователь данной сессии для новой строки получает значение 4 для колонки id с учетом того, что генератор последовательности (Sequence Generator) уже выдал значение 3 для пользователя первой сессии.

select * from test_identity; ID CITY_NAME ---- ---------- 1 Dushanbe 2 Moscow 4 Astana 

После выполнения commit в каждой сессии можно получить такой результат из сессии любого пользователя:

select * from test_identity; ID CITY_NAME ----- ---------- 3 Tashkent 1 Dushanbe 2 Moscow 4 Astana

Это означает, что данные, добавленные в первой сессии и данные, добавленные во второй сессии, после совершения commit объединились в одной таблице, сохранив лишь уникальные значения для колонки id.

3.3.3. Применение атрибутов Sequence Generator.

Как было описано ранее в данном посте, при использовании IDENTITY можно задать значение параметров генератора последовательностей (Sequence Generator). Приводится пример создания таблицы, где определяются значения двух параметров Sequence Generator – start with и increment by. В приведенном ниже примере генератор последовательности будет генерировать числа для колонки id начиная с числа 5 и каждое следующее число будет увеличиваться с шагом 5:

create table test_identity ( id number generated always as identity (start with 5 increment by 5), city_name varchar2(40) not null); Table TEST_IDENTITY created.

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

insert into test_identity (city_name) values ('Dushanbe'); 1 row inserted. insert into test_identity (city_name) values ('Moscow'); 1 row inserted. insert into test_identity (city_name) values ('Tashkent'); 1 row inserted.

Выполняется выборка данных из таблицы:

select * from test_identity; ID CITY_NAME ----- ---------- 5 Dushanbe 10 Moscow 15 Tashkent 

Результат запроса показывает, что генератор последовательности для колонки id с IDENTITY сгенерировал числа начиная с пяти и каждое последующее число увечил на пять.

На этом завершается описание трех способов реализации автоматической нумерации (автоинкремент) в базах данных Oracle.

Как сделать автозаполнение id в sql

alter table tTable add a int key auto_increment;

ALTER TABLE ‘tTable’ ADD COLUMN ‘a’ INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

Перед моим вышеприведенным запросом.

Гораздо интереснее переменные auto_increment_increment и auto_increment_offset.
Первая управляет шагом инкремента, а вторая начальным смещением.
Это очень полезно при настройке репликации и масштабирования баз.

Добавить заметку

Версия для печати

Последние заметки

— 21.10.2023 Защита от подмены серверных TLS-сертификатов в результате MITM-атаки провайдером

— 15.10.2023 Уменьшение жёстко определённого размера окна приложения в формате AppImage

— 12.10.2023 Определение IP-адреса пользователя в Telegram через голосовой вызов

— 14.09.2023 Установка в мобильной версии Firefox любого расширения от десктоп-версии

— 10.09.2023 Часто используемые параметры sysctl, касающиеся настройки сети в Linux

— 09.08.2023 Запуск Linux на Android-телефоне с SD-карты без перепрошивки

— 18.04.2023 Перевод шифрованного раздела на LUKS2 и более надёжную функцию формирования ключа

— 31.03.2023 Пример правил nftables с реализацией port knoсking для открытия доступа к SSH

— 27.02.2023 Обновление сертификатов oVirt

— 20.11.2022 Решение проблемы со шрифтами в Steam после выставления времени после 2038 года

RSS | Следующие 15 записей >>

Как создать поле со свойством auto_increment в Oracle

Обновлено: 17.09.2018 Опубликовано: 29.08.2016

В СУБД MySQL есть возможность использовать свойство auto_increment для числового поля — оно автоматически добавляет единицу числовому полю для следующей строки при вставке. Таким образом система самостоятельно генерирует уникальный идентификатор для записи. В Oracle данную функциональность можно реализовать при помощи триггера (ранее версии 12с) или свойства IDENTITY (с версии 12c).

Триггер (до Oracle 12c)

В данном примере, наша таблица будет называться ai_table, а поле, которому необходимо добавить возможность auto_increment — id.

Создаем последовательность следующей SQL-командой:

CREATE SEQUENCE sq_ai_table
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

* в данном примере создана последовательность с названием sq_ai_table. Начинается последовательность с 1 и каждый шаг будет добавлять тоже 1.

Теперь создаем сам триггер:

CREATE OR REPLACE TRIGGER tr_ai_table before INSERT ON ai_table FOR each row
BEGIN
SELECT sq_ai_table.NEXTVAL
INTO :new.id
FROM dual;
END;

* как видим, создан триггер с названием tr_ai_table для таблицы ai_table. При каждой INSERT команде будет браться число из нашей созданной последовательности и подставляться в поле id.

IDENTITY (Oracle 12c и более поздние)

Начиная с версии Oracle 12c есть возможность создать таблицу со свойством поля IDENTITY, которое является аналогом auto increment или autonumber.

Пример создания таблицы:

CREATE TABLE ai_table
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(512) NOT NULL,
UNIQUE (id, name)
)

* где id — поле со свойством автоинкремента с шагом 1 и начальным значением 1.