2.24. Работа с GUID полями

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

При работе с GUID полями мы получаем достаточно преимуществ и недостатков. В этой главе мы рассмотрим и то и другое, а также примеры обхода недостатков.

Давайте для иллюстрации примеров с GUID полями создадим две связанные таблицы: типы телефонов и номера телефонов. Подобные таблицы у нас уже существуют в тестовой базе данных, поэтому я взял их копию, заменил тип ключевых полей на uniqueidentifier и добавил к именам таблиц и ограничений префикс _G. Все это в виде SQL сценария можно увидеть в листинге 2.14.

Листинг 2.14. Создание таблиц, связанных через uniqueidentifier поля

-- Создаем таблицу типов телефонов
CREATE TABLE tbPhoneType_G
(
 idPhoneType uniqueidentifier DEFAULT NEWID(), 
 vcTypeName varchar(20),

 CONSTRAINT PK_idPhoneType_G PRIMARY KEY (idPhoneType),
)

-- Создаем таблицу телефонов
CREATE TABLE tbPhoneNumbers_G
(
  -- Описание полей
  idPhoneNumbers uniqueidentifier DEFAULT NEWID(), 
  idPhoneType uniqueidentifier, 
  vcPhoneNumber varchar(15),

  -- Описание ключей
  CONSTRAINT PK_idPhoneNumbers_G PRIMARY KEY (idPhoneNumbers),
  CONSTRAINT FK_idPhoneType_G FOREIGN KEY (idPhoneType)
     REFERENCES tbPhoneType_G (idPhoneType), 

  -- Описание ограничений
  CONSTRAINT check_vcPhonenumber_G CHECK (vcPhonenumber LIKE 
    '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')
)

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

Следующие два примера добавляют в таблицу типы телефонов сотовый и домашний:

-- Добавления типа телефона - сотовый
INSERT INTO tbPhoneType_G
VALUES (NewID(), 'Сотовый')

-- Добавляем домашний тип телефона
INSERT INTO tbPhoneType_G (vcTypeName)
VALUES ('Домашний')
Теперь посмотрим, что у нас хранится в таблице:
SELECT * 
FROM tbPhoneType_G
В моем случае результат получился следующим:
idPhoneType                            vcTypeName
-------------------------------------------------------
2A730DD9-3F89-48FD-A7C6-7FACDE496D2E   Домашний	
BA80361E-1398-4C58-9DF9-EC25C18A2439   Сотовый	

Мне повезло. Я наглядно смог увидеть, что домашний тип телефона добавлялся позже, а при отображении он стоит первым, потому что для него сгенерирован меньший идентификатор. Это и есть недостаток, который невозможно контролировать. Единственный удачный выход – добавить в таблицу типов еще одно поле, которое будет иметь автоматически увеличиваемое значение.

Давайте очистим таблицу (DELETE FROM tbPhoneType_G) и добавим новое поле

ALTER TABLE tbPhoneType_G
ADD [id] int IDENTITY(1, 1)

Снова добавьте в таблицу записи типов телефонов. Чтобы просмотреть все строки в порядке добавления в таблицу, необходимо отсортировать вывод по полю "id":

SELECT * 
FROM tbPhoneType_G
ORDER BY [id]

От одного недостатка избавились. Давайте двигаться дальше. Теперь посмотрим, как можно добавлять записи в таблицу телефонов. В таблице телефонов нам нужно указать идентификатор типа и номер телефона. Проблема возникает именно с первым значением, потому что вводить идентификаторы вручную проблематично. Эта проблема решается следующим образом:

-- Объявляем переменную
DECLARE @TypeID uniqueidentifier

-- Записываем в нее идентификатор домашнего телефона
SELECT @TypeID=idPhoneType 
FROM tbPhoneType_G 
WHERE vcTypeName='Домашний'

-- Добавляем запись с номером телефона
INSERT INTO tbPhoneNumbers_G (idPhoneType, vcPhoneNumber)
VALUES (@TypeID, '(000) 912-12-12')

В этом примере сначала объявляется переменная @TypeID типа uniqueidentifier. Затем в эту переменную с помощью запроса SELECT записывается идентификатор домашнего типа телефона. Теперь у нас есть все необходимо для добавления записи, что происходит в последнем запросе.

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

SELECT * 
FROM tbPhoneType_G pt, tbPhoneNumbers_G pn
WHERE pt.idPhoneType=pn.idPhoneType

Допустим, что вы четко знаете, что GUID идентификатор типа телефона равен 09F972F4-CFF9-4E96-9B7C-14AA04B835F3. Как его можно использовать напрямую? Чтобы SQL запрос воспринял это число как GUID, его необходимо заключить в фигурные скобки и передать в виде строки.

Следующий пример объявляет переменную @TypeID типа uniqueidentifier и с помощью SET помещает в эту переменную явное значение, которое потом будет использоваться при добавлении записи с номером телефона:

-- Объявляем переменню
DECLARE @TypeID uniqueidentifier

-- Записываем в переменную значение
SET @TypeID='{09F972F4-CFF9-4E96-9B7C-14AA04B835F3}'

-- Вставляем запись
INSERT INTO tbPhoneNumbers_G (idPhoneType, vcPhoneNumber)
VALUES (@TypeID, '(000) 112-10-10')

Обратите внимание, что значение GUID сначала заключается в фигурные скобки и после этого еще и в одинарные кавычки. Если что-то из этого не указать, сервер вернет ошибку.

Зная идентификатор, можно добавить строку одной командой INSERT INTO. Например:

INSERT INTO tbPhoneNumbers_G (idPhoneType, vcPhoneNumber)
VALUES ('{09F972F4-CFF9-4E96-9B7C-14AA04B835F3}', '(000) 222-10-10')

Благодаря использованию GUID идентификаторов, упрощается добавление записей сразу в две таблицы. Например, в следующем запросе в таблицу типов добавляется новая строка "Сотовый личный" и тут же мы добавляем номер телефона с соответствующим типом:

-- Объявляем переменную
DECLARE @TypeID uniqueidentifier

-- Генерируем GUID значение 
SET @TypeID=NewID()

-- Вставляем запись в таблицу типов
INSERT INTO tbPhoneType_G
VALUES (@TypeID, 'Сотовый личный')

-- Вставляем запись в таблицу телефонов
INSERT INTO tbPhoneNumbers_G (idPhoneType, vcPhoneNumber)
VALUES (@TypeID, '(901) 111-11-11')

В этом примере, в переменную @TypeID заносится результат выполнения функции NewID(), т.е. сгенерированное значение. После этого, с этим идентификатором добавляются строки в таблицы типов телефонов и в таблицу номеров телефонов.

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

Листинг 2.15. Добавление типа только если это нужно

Объявляем переменную
DECLARE @TypeID uniqueidentifier

-- Проверяем, есть ли нужная запись в таблице
IF NOT EXISTS (SELECT idPhoneType
               FROM tbPhoneType_G 
               WHERE vcTypeName='Сотовый личный')
 BEGIN
  -- Если не существует, то генерируем GUID и добавляем и добавляем
  SET @TypeID=NewID()
  INSERT INTO tbPhoneType_G
  VALUES (@TypeID, 'Сотовый личный')
  print 'Создаем тип "Сотовый личный"';
 END
ELSE
 BEGIN
  -- Если существует, то ищем GUID в таблице
  print 'Такой тип уже существует, используем его';

  SELECT @TypeID=idPhoneType
  FROM tbPhoneType_G 
  WHERE  vcTypeName='Сотовый личный'
 END

-- Вставляем строку
INSERT INTO tbPhoneNumbers_G (idPhoneType, vcPhoneNumber)
VALUES (@TypeID, '(901) 111-11-11')

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

Как видите, проблемы при использовании GUID есть, но они решаемы. А ведь преимущества от использования GUID затмевают все недостатки. Главное преимущество – возможность самостоятельного задания первичного ключа, без риска нарушения целостности. Это преимущество особенно проявляется при разработке клиентского приложения на языках высокого уровня, например, Delphi или C++. На этих языках пользователь может вводить данные на стороне клиента и сохранять их в памяти. Когда данные накопятся, их можно одним блоком сохранить на сервере.

Пока данные находятся в памяти клиентской машине, первичный ключ с автоматически увеличиваемым полем не может содержать значения, а значит нельзя создавать связей. Если первичный ключ имеет тип GUID, то программа может сама создать значение для ключа и использовать его для создания связей в памяти клиентской машине с данными, которые еще даже не существуют на сервере. Сейчас уже почти во всех языках есть собственные возможности генерирования GUID значений, результат которых абсолютно одинаков.