Создание автоинкрементных столбцов

Автоинкрементный столбец (autoincrement field) – это целочисленный столбец, значение которого автоматически увеличивается или уменьшается с постоянным шагом при добавлении новой строки. Например, у первой строки этот столбец =1, у второй 2, у третьей 3, и т.д. Когда строка удаляется (например, с номером 3), значение 3 все равно больше использоваться не будет, поскольку автоинкрементный столбец всегда только увеличивается, не возвращаясь назад. Автоинкрементные столбцы используются для создания суррогатных первичных ключей.

Создание автоинкрементных столбцов в Microsoft SQL Server

Автоинкрементный столбец объявляется при описании столбца командами CREATE TABLE или ALTER TABLE с помощью конструкции

IDENTITY (начальное_значение, шаг_приращения)

Например,

CREATE TABLE Records(

RecNum INT IDENTITY (0,1) PRIMARY KEY,

/* первичный ключ - это автоинкреметное поле

с начальным значением 0 и шагом приращения +1*/

Field_1 CHAR (10),...-- описание прочих столбцов

);

Тип автоинкрементного столбца должен быть целочисленным (tinyint, smallint, int, bigint) или с фиксированной запятой (decimal (p, 0); numeric (p, 0)). Свойство IDENTITY не применяется совместно со свойствами DEFAULT и NULL. Разрешается иметь только один автоинкрементный столбец в таблице.

Для генерации значений автоинкрементного столбца сервер использует скрытую переменную-счетчик. Сервер следит за тем, чтобы при удалении какой-либо записи ее порядковый номер не был использован повторно.

Создание автоинкрементных столбцов в MySQL

MySQL разрешает иметь в таблице только один автоинкрементный столбец (счётчик). Он объявляется флагом AUTO_INCREMENT при создании таблицы. Тип столбца-счетчика – беззнаковый целый.

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

Тип таблицы и начальное значение счетчика можно задать табличными опциями

TYPE = тип_таблицы

AUTO_INCREMENT = начальное_значение /* действует только

на таблицы типа MyISAM */

Опция тип_таблицы определяет формат хранения таблицы и функциональные возможности. MySQL версии 3.23 поддерживает семь типов таблиц: ISAM, MyISAM (по умолчанию), Heap, Merge, InnoDB, Gemini, BerkleyDB (BDB). Поддержка транзакций реализована только в последних трех типах таблиц. Табличные опции указываются после операторов CREATE TABLE или ALTER TABLE, например:

CREATE TABLE Records(

/* столбцы */

RecNum INT UNSIGNED NOT NULL AUTO_INCREMENT,

Field_1 CHAR (10) NULL,

... /* описание прочих столбцов */

/* ограничения целостности */

PRIMARY KEY (RecNum)

)

/* табличные опции */

TYPE = MYISAM

AUTO _ INCREMENT = 100

Создание автоинкрементных столбцов в Firebird

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

Генератор (generator) – это специальный объект базы данных. Он представляет собой целочисленную 4-байтовую переменную.

Генератор создается командой

CREATE GENERATOR Имя_генератора

По умолчанию значение нового генератора равно 0. Его можно изменить командой

SET GENERATOR Имя_генератора TO новое_значение

Новое значение может быть числом от –2³¹ до +(2³¹–1).

Для получения следующего значения генератора предназначена функция

GEN_ID (Имя_генератора, шаг_приращения)

Функция прибавляет шаг _ приращения к текущему значению генератора и возвращает полученное число. Шаг приращения может быть положительным или отрицательным.

Для создания автоинкрементного столбца нужно предварительно создать генератор. Значение генератора будет заноситься в ячейку автоинкрементного столбца при добавлении новой строки. Эта операция автоматически выполняется триггером.

Триггер (trigger) – блок команд SQL, которые автоматически выполняются сервером в ответ на операцию с таблицей. Каждый триггер привязан к конкретной таблице. По типам операций, вызывающих запуск триггера, триггеры делятся на три типа:

· INSERT – добавление новых строк,

· UPDATE – модификация существующих строк,

· DELETE – удаление строк.

По времени запуска триггеры Firebird делятся на два типа:

· BEFORE – триггер запускается до внесения изменений в таблицу;

· AFTER – триггер запускается после фиксации изменений в таблице.

Если с одним и тем же событием (например, модификацией строк таблицы) связано несколько триггеров, то они вызываются по очереди в соответствии со своей позицией (0,1,2,…).

Триггер создается оператором CREATE TRIGGER

CREATE TRIGGER Имя_триггера FOR Имя_таблицы

{ BEFORE | A FTER } { INSERT | UPDATE | DELETE }

[ POSITION значение_позиции ]

AS BEGIN

< тело триггера. Операторы разделяются; >

END < символ-ограничитель >

Символ-ограничитель по умолчанию – точка с запятой. Но поскольку ";" используется в теле триггера, то требуется переопределить ограничитель. Для этого используется оператор

SET TERM новый _ ограничитель

Новый_ограничитель – это произвольная (в пределах разумного) подстрока.

Ниже приведен пример создания триггера на вставку строк. Триггер заносит в ячейку автоинкрементного столбца новое значение генератора.

/*создаем таблицу – столбец RecNum будет автоинкрементным*/

CREATE TABLE Records(

RecNum INT NOT NULL PRIMARY KEY,

...); /* прочие столбцы */

/* создаем генератор (по умолчанию равен 0) */

CREATE GENERATOR RecNum_Gen;

/* создаем триггер: предварительно

меняем символ-ограничитель с ";" на "!!" */

SET TERM!!;

/* объявление триггера */

CREATE TRIGGER CreateRecNum FOR Records

BEFORE INSERT

POSITION 0

AS BEGIN

NEW.RecNum = GEN_ID (RecNum_Gen, 1);

END!!

/* меняем символ-ограничитель обратно */

SET TERM;!!

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

NEW.RecNum = GEN_ID (RecNum_Gen, 1);

Функция GEN_ID автоматически увеличивает значение генератора на 1, и возвращает его новое значение.