AUTO_INCREMENT поле в Oracle

Знакомые с MySQL программисты при первом знакомстве с СУРБД Oracle, вероятно, удивятся отсутствию в ней такой приятной возможности, как создание auto_increment полей, то есть полей, численное значение которых автоматически увеличивается при создании новых записей. Как правило, auto_increment поля используются как primary key - первичные ключи для таблиц.

В MySQL эти поля создаются буквально на лету:

CREATE TABLE users(
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL, PRIMARY KEY (id)
);

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

INSERT INTO userns(name) VALUES ("sterx"),("alex"),("kime");

и для каждой из созданных записей в случае MySQL будет автоматически вычислен id.

В Oracle auto_increment поля создаются несколько иначе, используются так называемые последовательности (sequence), или, другими словами, счетчики. Создадим последовательность:

CREATE SEQUENCE users_seq
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE;

Как видно из объявления, последовательность начинается с 1 (конечно, при необходимости, можно задать другое значение), шаг последовательности также равен 1, последовательность не имеет максимального значения.

Теперь при создании записей в таблице нам необходимо присваивать полю id текущее значение счетчика-последовательности (или следующее значение счетчика в зависимости от реализации). Это можно сделать двумя способами - создать триггер, отслеживающий создание записей в таблице и присваивающий полю id значение счетчика, либо в самой команде INSERT использовать значение счетчика для задания значения id. Использование триггера с одной стороны усложняет реализацию, с другой стороны упрощает команды INSERT, поэтому выбор способа установки id оставлю на усмотрение читателя.

Рассмотрим оба варианта.

Использование триггера

create or replace trigger users_id_trg
before insert on users
for each row
begin
  if :new.id is null then
    select users_seq.nextval into :new.id from dual;
  end if;
end;

В теле триггера мы проверяем, что id новой записи не установлен пользователем, так как по своим причинам пользователь может захотеть установить значение id самостоятельно, не полагаясь на триггер. Если это не так - вычисляется новое значение счетчика и id устанавливается автоматически.

Использование хитрого INSERT

INSERT INTO users (id, name) VALUES (users_seq.nextval, 'sterx');

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

PS. Созданные последовательности и триггеры легко удалить, поэтому не бойтесь с ними работать.

DROP SEQUENCE users_seq;
DROP TRIGGER users_id_trg;