Использование автоматической нумерации в СУБД 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 Dushanbe2 Moscow
Автоматическая нумерация.
Общий принцип механизма автоматической нумерации можно описать следующим образом: внутри базы к каждой таблице создается и привязывается отдельный счетчик, который увеличивается на единицу при вставке новой строки, а получившееся значение записывается в ту колонку, которое помечено как автонумерация (автоинкремент).
У различных СУБД этот механизм реализован по-своему. В данном посте рассматривается применение данного механизма применительно к СУБД Oracle.
В Oracle автонумерацию можно реализовать несколькими способами. Например, с помощью:
- Последовательности (Sequence),
- Комбинации последовательности и триггера (Trigger),
- Использования колонки с 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 Dushanbe2 Moscow3 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 Dushanbe2 Moscow3 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 Dushanbe2 Moscow3 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 Dushanbe2 Moscow3 Tashkent4 Astana5 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 Dushanbe2 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 Tashkent1 Dushanbe2 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 Dushanbe2 Moscow4 Astana
После выполнения commit в каждой сессии можно получить такой результат из сессии любого пользователя:
select * from test_identity;ID CITY_NAME----- ----------3 Tashkent1 Dushanbe2 Moscow4 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 Dushanbe10 Moscow15 Tashkent
Результат запроса показывает, что генератор последовательности для колонки id с IDENTITY сгенерировал числа начиная с пяти и каждое последующее число увечил на пять.
На этом завершается описание трех способов реализации автоматической нумерации (автоинкремент) в базах данных Oracle.