45793 (К вопросу об идентификаторах)

К вопросу об идентификаторах

Автоинкременты и все, все, все…

Иван Бодягин

О чем речь

Уникальная идентификация записей в таблице, является практически основой реляционных СУБД. Вообще в реляционной теории предполагается, что если две записи ни чем друг от друга не отличаются, то это явная избыточность, и количество таких записей можно сократить до одной. Собственно вопросам этой самой идентификации, каковых возникает на удивление много, и посвящен этот FAQ.

Идентификация

Как уже говорилось уникальная идентификация записей – это основа реляционных СУБД. Вообще данный вопрос довольно тесно связан с давним теоретическим спором «Суррогатные ключи» vs. «Естественные ключи». Но поскольку существует замечательная статья Анатолия Тенцера “Естественные ключи против искуственных ключей”, то не думаю, что здесь имеет смысл освещать этот вопрос. Вне зависимости от используемого подхода, очень часто, по тем или иным причинам, данные, относящиеся непосредственно к приложению, не удается заведомо однозначно разделить по записям. И логично, что в таких случаях прибегают к помощи сервера, который генерирует искусственные идентификаторы для каждой записи.

Особого внимания заслуживает термин «уникальный». Уникальность серверного идентификатора имеет свои границы. В каких-то случаях гарантируется уникальность в пределах таблицы, то есть если не предпринимать специальных усилий, то есть шанс получить одинаковые идентификаторы в разных таблицах, что не всегда приемлемо. В каких-то случаях этот идентификатор гарантировано уникален в пределах БД, чего тоже не всегда бывает достаточно. И, наконец, есть идентификаторы, гарантирующие уникальность в пределах всех СУБД вообще…

Способ генерации и вид этого идентификатора стандартом не оговорен, да и вообще стандарт подобными мелочами не занимается, поэтому каждый производитель СУБД реализует эту функциональность так, как ему удобно. В итоге, мы имеем потрясающий набор различных способов, выудить из сервера уникальный идентификатор, беда только в том, что у каждого сервера этот способ свой. Однако, несмотря на все многообразие подходов, практически во всех СУБД, в том или ином виде, реализована автоматическая генерация последовательных идентификаторов, это так называемые «автоинкременты». Иными словами, при обращении к серверу за таким идентификатором гарантируется, что возвращенное число будет больше, чем число, полученное при предыдущем обращении.

ПРИМЕЧАНИЕ Говоря «больше», я несколько упрощаю. В принципе, в большинстве СУБД, ничто не мешает задать и отрицательное приращение. То есть, правильнее было бы сказать, что каждое последующее число отличается от предыдущего на величину заданного приращения, которое вполне может быть отрицательным, но думаю, что в данном случае лучше упростить.

Microsoft SQL Server

Эта СУБД предоставляет два способа получить обеспечить уникальность записи. Один работает в пределах таблицы и является автоинкрементным, а другой замахивается на уникальность «вообще», и в принципе подобное решение покрывает практически все потребности в уникальных идентификаторах.

Автоинкремент

Начнем с обычного автоинкремента. В отличие от большинства других СУБД, здесь эта функциональность реализована не в виде отдельной функции, а в виде свойства таблицы, поэтому и гарантируется уникальность лишь в пределах таблицы, а не БД или экземпляра сервера. Для того чтобы не болела голова об уникальности записей, один из столбцов в таблице объявляется идентификатором, после этого, при добавлении каждой новой записи, в этом столбце автоматически появляется число, которое заведомо больше, чем то, которое было сгенерировано для предыдущей добавленной записи. Вообще здесь есть ряд тонкостей, но о них чуть позже.

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

IDENTITY [ ( seed , increment ) ]

Здесь, как не сложно догадаться, seed – это самое первое значение, а increment – это величина, которая каждый раз будет прибавляться к предыдущему значению. По умолчанию seed и increment равны единице, то есть выставить у столбца свойство IDENTITY, равноценно выставлению IDENTITY(1,1). Ключевое слово IDENTITY может быть указано при создании CREATE TABLE, или изменении таблицы ALTER TABLE. При этом тип столбца должен быть tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0), то есть автоникремент должен быть целочисленным. Следует так же помнить, что только один столбец может быть объявлен IDENTITY. Таким образом, создание таблицы с автоинкрементным полем выглядит примерно так:

CREATE TABLE Ident_table (ID int IDENTITY(1, 1), some_values varchar(50))

Здесь столбец ID объявлен автоинкрементным, и при каждом добавлении новой записи в таблицу, в него автоматически будет записываться новое число, хотя бы на единицу большее того, что было записано при предыдущем добавлении.

INSERT INTO Ident_table (some_values) VALUES ('value 1') INSERT INTO Ident_table (some_values) VALUES ('value 2') SELECT * FROM Ident_table --- результат: ID some_values ----------- -------------------------------------------------- 1 value 1 2 value 2 (2 row(s) affected)

При этом явно писать значения в автоинкрементное поле нельзя, произойдет ошибка, и оператор вставки не отработает.

INSERT INTO Ident_table (ID, some_values) VALUES (3, 'value 2') --- результат: Cannot insert explicit value for identity column in table 'Ident_table' when IDENTITY_INSERT is set to OFF.

Однако прямо в сообщении об ошибке есть подсказка как это ограничение обойти. Для того чтобы писать напрямую в автоинкрементный столбец, необходимо установить свойство IDENTITY_INSERT в ON.

SET IDENTITY_INSERT Ident_table ON GO INSERT INTO Ident_table (ID, some_values) VALUES (5, 'value 5') SELECT * FROM Ident_table --- результат: ID some_values ----------- -------------------------------------------------- 1 value 1 2 value 2 5 value 3 (3 row(s) affected)

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

INSERT INTO Ident_table (some_values) VALUES ('value 4') --- результат: Explicit value must be specified for identity column in table 'Ident_table' when IDENTITY_INSERT is set to ON.

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

ПРЕДУПРЕЖДЕНИЕ Следует помнить, что если автоинкрементное поле заполняется самостоятельно и на это поле не установлено никаких дополнительных требований уникальности, то запросто можно записать туда несколько одинаковых значений.

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

SET IDENTITY_INSERT Ident_table ON GO INSERT INTO Ident_table (some_values) VALUES ('value 4') SELECT * FROM Ident_table --- результат: ID some_values ----------- -------------------------------------------------- 1 value 1 2 value 2 5 value 3 6 value 4 (4 row(s) affected)

Все это конечно здорово, но, как правило, просто уникально идентифицировать запись недостаточно, необходимо еще связать эту запись с записью из другой таблицы по этому самому идентификатору. А для этого надо уметь получать этот идентификатор сразу же после его генерации. Для выполнения этой задачи в Microsoft SQL Server существуют 3 функции: @@IDENTITY, SCOPE_IDENTITY() и IDENT_CURRENT().

Функция @@IDENTITY возвращает последнее значение, записанное севером в автоинкрементный столбец в текущей сессии. Что это означает? Если между вызовом INSERT и вызовом @@IDENTITY успеет пролезть вставка из другой сессии, то @@IDENTITY вернет идентификатор, который был записан при первой вставке. То есть, при пользовании @@IDENTITY нет необходимости заботиться о том, что параллельные вставки будут мешать друг другу при получении правильных идентификаторов, сервер все сделает сам.

---- Session 1: INSERT INTO Ident_table (some_values) VALUES ('value 5') ---- Session 2: INSERT INTO Ident_table (some_values) VALUES ('value 6') ---- Session 1: SELECT @@IDENTITY as [Last ID in session] SELECT * FROM Ident_table --- результат: Last ID in session ---------------------------------------- 7 (1 row(s) affected) ID some_values ----------- ---------------------------- ... ... 6 value 4 7 value 5 8 value 6 (6 row(s) affected)

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

Для того чтобы избежать таких неприятностей, служит SCOPE_IDENTITY(), который возвращает значение, записанное сервером в автоинкрементный столбец не только в рамках сессии, но и в рамках текущего пакета (batch).

-- еще одна табличка с автоинкрементом CREATE TABLE Ident2(ID int IDENTITY(0, -2), value varchar(50)) GO -- триггер на вставку к первоначальной табличке CREATE TRIGGER IdentTrigger ON Ident_table FOR INSERT AS INSERT INTO Ident2 (value) VALUES(GetDate()) GO -- добавление еще одной записи INSERT INTO Ident_table (some_values) VALUES ('value 7') -- наслаждение результатом... SELECT @@IDENTITY as [Last ID in session (@@IDENTITY)] SELECT SCOPE_IDENTITY() as [Last ID in batch (SCOPE_IDENTITY())] SELECT * FROM Ident_table --- результат: Last ID in session (@@IDENTITY) ---------------------------------------- 0 Last ID in batch (SCOPE_IDENTITY()) ---------------------------------------- 9 ID some_values ----------- ---------------------------- ... ... 8 value 6 9 value 7 (7 row(s) affected)

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

--- Session 1: INSERT INTO Ident_table (some_values) VALUES ('value 8') --- Session 2: INSERT INTO Ident_table (some_values) VALUES ('value 9') --- Session 1: SELECT @@IDENTITY as [Last ID in session], SCOPE_IDENTITY() as [Last ID in batch], IDENT_CURRENT('Ident_table') as [Last ID in IdentTable] SELECT * FROM Ident_table SELECT * FROM Ident2 --- результат: Last ID in session Last ID in batch Last ID in IdentTable ------------------ ---------------- --------------------- -2 10 11 ID some_values ----------- ---------------------------------------------- ... ... 9 value 7 10 value 8 11 value 9 (9 row(s) affected) ID value ----------- ---------------------------------------------- 0 1961-02-01 19:15 -2 1961-02-01 19:30 (2 row(s) affected)

Собственно этот пример и демонстрирует различия всех трех способов получения записанного сервером значения автоинкремента. Эти способы покрывают практически все потребности в автоинкрементах, которые могут возникнуть при разработке.