Вставка строк в таблицу, содержащую автоинкрементируемое поле

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

Ниже приводится пример создания таблицы Printer_Inc с автоинкрементируемым столбцом (code) в MS SQL Server.

CREATE TABLE Printer_Inc
(
code int IDENTITY(1,1) PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);

Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй, — какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце code значение 1, вторая — 2 и т. д.

Поскольку в поле code значение формируется автоматически, оператор

INSERT INTO Printer_Inc
VALUES (15, 3111, 'y', 'laser', 599);

приведет к ошибке, даже если в таблице нет строки со значением в поле code, равным 15. Поэтому для вставки строки в таблицу просто не будем указывать это поле точно так же, как и в случае использования значения по умолчанию, то есть

INSERT INTO Printer_Inc (model, color, type, price)
VALUES (3111, 'y', 'laser', 599);

В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, так как значение автоинкрементируемого поля, как правило, не несет никакой смысловой информации; главное, чтобы оно было уникальным.

Однако бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи «один-ко-многим» со стороны «один». Таким образом, мы не можем допустить тут произвола. С другой стороны, не хочется отказываться от автоинкрементируемого поля, так как оно упростит обработку данных при последующей эксплуатации базы данных.

Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то не существует и единого подхода. Вот как это реализуется в MS SQL Server. Оператор

SET IDENTITY_INSERT < имя таблицы > { ON | OFF };

отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model, color, type, price)
VALUES (15, 3111, 'y', 'laser', 599);

Обратите внимание, что список столбцов в этом случае является обязательным, то есть мы не можем написать так:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc
VALUES (15, 3111, 'y', 'laser', 599);

ни, тем более, так:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(model, color, type, price)
VALUES (3111, 'y', 'laser', 599);

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

Важно отметить, что если значение 15 окажется максимальным в столбце code, то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование:

SET IDENTITY_INSERT Printer_Inc OFF;

Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model,color,type,price)
SELECT * FROM Printer;

По поводу автоинкрементируемых столбцов следует добавить следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, так как последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.

Рассмотрим теперь таблицу с единственным автоинкрементируемым столбцом (SQL Server):

CREATE TABLE test_Identity(
    id int IDENTITY(1,1) PRIMARY KEY
);

Как вставить в нее строки? Попытка не указывать значение

INSERT INTO test_Identity VALUES();

или использовать значение по умолчанию

INSERT INTO test_Identity VALUES(DEFAULT);

к успеху не приводит - ошибка.

Понятно, что мы можем вставить конкретное значение, если отключим счетчик:

SET IDENTITY_INSERT test_Identity ON;  
INSERT INTO test_Identity(id) VALUES(5);  
SELECT * FROM test_Identity;

но тогда в нем нет для нас никакого смысла.

Уточним вопрос: как вставить в таблицу именно последовательные значения счетчика? Оказывается, что ответ лежит на поверхности, а именно, в стандартном синтаксисе:

SET IDENTITY_INSERT test_Identity OFF;
INSERT INTO test_Identity DEFAULT VALUES;

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

Обратимся теперь к другим СУБД, которые имеют в своем арсенале автоинкрементируемые столбцы.

MySQL

MySQL не поддерживает предложения DEFAULT VALUES. Вставить строку со значениями по умолчанию в таблицу можно другим стандартным способом, используя ключевое слово DEFAULT для каждого столбца таблицы - VALUES(DEFAULT, DEFAULT, …).

А как здесь вставить очередное значение счетчика в единственный автоинкрементируемый столбец?

CREATE TABLE test_Identity (
id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
);

Очень просто. Оказывается будут работать те интуитивные приемы, которые мы безуспешно пытались применить в случае SQL Server, а именно, так

insert into test_Identity values();

или так

insert into test_Identity values(default);

После выполнения обоих этих операторов получим:

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

insert into test_Identity values(8);
insert into test_Identity values(default);
select * from test_Identity;

PostgreSQL

create table identity_table(id serial primary key);

Для вставки очередных значений счетчика мы можем воспользоваться следующими рассмотренными выше приемами:

insert into identity_table default values;
insert into identity_table values(default);
insert into identity_table(id) values(default);
select * from identity_table;

Однако, если вставить конкретное значение (превышающее максимальное значение, достигнутое счетчиком)

insert into identity_table(id) values(5);

и продолжить заполнять значения счетчика,

insert into identity_table values(default);

то нумерация продолжается не с максимального значения, а с последнего достигнутого при генерации:

При этом, когда счетчик достигает 5 при генерации очередного значения, получаем ошибку, связанную с нарушением ограничения первичного ключа:

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

create table identity_table_wo(id serial);
insert into identity_table_wo(id) values(default);  
insert into identity_table_wo(id) values(2);  
insert into identity_table_wo(id) values(default),(default);  
select * from identity_table_wo;

Как сбросить значение счетчика в заданное значение (MySQL)?

Воспользуемся таблицей, рассмотренной выше, и вставим в неё 3 строки.

CREATE TABLE test_Identity 
(
    id int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY  (id)
);
INSERT INTO test_Identity VALUES(),(),();
SELECT * FROM test_Identity;

Если мы удалим последнюю строку, нумерация продолжится не с 3, а с 4. Т.е. последнее значение счётчика сохраняется и используется при последующем добавлении строк:

DELETE FROM test_Identity WHERE id=3;
INSERT INTO test_Identity VALUES();
SELECT * FROM test_Identity;

Возникает вопрос: “А можно ли сделать так, чтобы нумерация продолжилась с последнего имеющегося значения?” Оставляя в стороне вопрос о том, зачем это нужно, ответим - можно. Но устанавливать это значение счётчика нужно вручную. Итак,

DELETE FROM test_Identity WHERE id=4;
ALTER TABLE test_Identity AUTO_INCREMENT = 3;
INSERT INTO test_Identity VALUES(),(),();
SELECT * FROM test_Identity;

Столбцы IDENTITY в PostgreSQL

Начиная с PostgreSQL 10, появилась возможность объявления столбца identity. Вот синтаксис, который несколько отличается от используемого в SQL Server:

<имя_столбца> <тип> GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ (START WITH <старт> INCREMENT BY <приращение> ) ]

По умолчанию старт и приращение равны 1. Начнем с параметра ALWAYS и создадим аналог таблицы Printer_Inc, которая использовалась ранее для примеров в SQL Server:

CREATE TABLE Printer_Inc
(
    code INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    model VARCHAR (4) NOT NULL ,
    color CHAR (1) NOT NULL ,
    type VARCHAR (6) NOT NULL ,
    price FLOAT NOT NULL
);

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

INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1000,'y','jet',600),
(1001,'n','laser',800);

Как видно, по умолчанию значения в столбце identity начинаются с 1 и увеличиваются с шагом 1.

Теперь попробуем вставить строку с заданным значением в столбце code:

INSERT INTO Printer_Inc VALUES
(3, 1003,'n','laser',850);

Сервер возвращает ошибку:

Такое поведение согласуется с поведением identity в SQL Server. Однако тут же дается совет использовать переопределение системного значения (OVERRIDING SYSTEM VALUE). Попробуем:

INSERT INTO Printer_Inc
OVERRIDING SYSTEM VALUE
VALUES(3, 1003,'n','laser',850);

А если, воспользовавшись этим способом, заменить строку с существующим значением в столбце code?

INSERT INTO Printer_Inc
OVERRIDING SYSTEM VALUE
VALUES(3, 1004,'n','laser',777.99);

Нет, не удается. Поскольку тут мы нарушаем ограничение первичного ключа, то получаем соответствующую ошибку.

Теперь проверим, откуда продолжится нумерация, если вставить значение не по порядку.

INSERT INTO Printer_Inc
OVERRIDING SYSTEM VALUE
VALUES(10, 1010,'n','laser',777.99);
INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1004,'y','jet',6500);

Если выполнить эти операторы по одному, а не в пакете, то получим:

Таким образом, нумерация продолжится с последнего достигнутого значения последовательного счетчика, что потенциально приведет к ошибке (нарушения ограничения первичного ключа) при достижении значения 10.

Давайте теперь попробуем сдвинуть начало отсчета и заодно изменить шаг приращения для существующей таблицы:

-- Удалим последние строки для чистоты эксперимента
DELETE FROM Printer_Inc WHERE code>3;
-- Изменим начальное значение счетчика
ALTER TABLE Printer_Inc
ALTER COLUMN code
SET START WITH 20;
-- Изменим величину приращения
ALTER TABLE Printer_Inc
ALTER COLUMN code
SET INCREMENT BY 10;
-- перезапустим счетчик
ALTER TABLE Printer_Inc
ALTER COLUMN code
RESTART;
-- Вставим пару строк
INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1004,'y','jet',680);
INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1005,'y','jet',670);
-- Посмотрим результат
SELECT * FROM Printer_Inc;

Теперь рассмотрим второй вариант (GENERATED BY DEFAULT), который допускает явную вставку значений в автоинкрементируемый столбец.

ALTER TABLE Printer_Inc
ALTER COLUMN code
SET GENERATED BY DEFAULT;
INSERT INTO Printer_Inc values
(40, 1006,'n','laser',880);
SELECT * FROM Printer_Inc;

Как видим, нам не пришлось для этого указывать OVERRIDING SYSTEM VALUE. А каким будет следующее значение?

INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1007,'y','jet',6600);

Т.е. PostgreSQL пытается продолжить нумерацию с последнего автоинкрементированного значения. Тогда придется передвинуть начальное значение и перезапустить identity:

ALTER TABLE Printer_Inc
ALTER COLUMN code
SET  START WITH 50;
ALTER TABLE Printer_Inc
ALTER COLUMN code
RESTART;
INSERT INTO Printer_Inc(model,color,type,price) VALUES
(1007,'y','jet',6600);
SELECT * FROM Printer_Inc;