В моей предыдущей статье я рассказал о разнице между замещающими ключами и естественными ключами. Замещающие ключи – это искусственно созданные ключи, которые не появляются естественным образом в данных. В этой статье я покажу вам, как генерировать замещающие ключи с использованием столбца идентификатора в SQL Server.
Что такое столбец идентификатора?
Столбец идентификатора – это один столбец в таблице, у которого установлено свойство столбца идентификатора. Когда у таблицы есть столбец идентификатора, этот столбец автоматически заполняется целочисленным значением каждый раз, когда в таблицу добавляется новая строка. Значение столбца идентификатора основано на начальном значении и значении приращения, связанных со столбцом.
Столбец идентификатора может быть установлен только для столбцов, объявленных как decimal, int, numeric, smallint, bigint или tinyint. Если свойство идентификатора связано с числовым или десятичным значением, масштаб должен быть установлен в 0. Кроме того, столбец должен быть определен так, чтобы не разрешать вставку NULL значений в него.
Создание таблицы со столбцом идентификатора
Чтобы определить столбец идентификатора при создании таблицы, вы можете установить свойство IDENTITY в операторе CREATE TABLE. Вот пример:
CREATE TABLE dbo.MyTable (
ID INT IDENTITY(1,1),
MyShortDesc varchar(20),
MyLongDesc varchar(500)
);В приведенном выше примере “ID” – это столбец идентификатора. Обозначение “IDENTITY(1,1)” указывает начальное значение и значение приращения для столбца идентификатора. Начальное значение используется для установки значения столбца ID для первой вставленной строки в таблицу, а значение приращения используется для заполнения значения столбца идентификатора для дополнительных строк, добавленных в таблицу.
Вы также можете создать столбец идентификатора при создании таблицы с использованием оператора SELECT с INTO-клаузой таблицы. Вот пример:
SELECT IDENTITY(int,1,1) AS ID, MyShortDesc, MyLongDesc
INTO dbo.MyTableNew
FROM dbo.MyTable;В этом примере синтаксис SELECT…INTO используется для создания новой таблицы с именем “MyTableNew” со столбцом идентификатора.
Изменение существующей таблицы для добавления столбца идентификатора
Если вам нужно добавить свойство идентификатора к существующему столбцу в существующей таблице или добавить новый столбец идентификатора в существующую таблицу, вы можете использовать оператор ALTER TABLE. Вот пример:
ALTER TABLE dbo.County ADD CountyID INT IDENTITY(1,1);В этом примере столбец “CountyID” добавляется в качестве нового столбца идентификатора в таблицу “County”. SQL Server автоматически пронумерует все существующие строки на основе свойства идентификатора.
Вставка записей в таблицу со столбцом идентификатора
При вставке записей в таблицу со столбцом идентификатора необходимо учесть, как заполняется столбец идентификатора. Если вы не указываете столбец идентификатора в операторе INSERT, он будет автоматически заполняться с использованием настроек свойства идентификатора. Вот пример:
INSERT INTO dbo.MyTable (MyShortDesc, MyLongDesc)
VALUES('PHRF','Pacific Handicap Racing Fleet');В этом примере столбец “ID” не указывается в операторе INSERT, потому что он будет автоматически заполнен.
Если вы хотите явно установить значение столбца идентификатора в операторе INSERT, вам нужно сначала установить опцию IDENTITY_INSERT в значение ON. Вот пример:
SET IDENTITY_INSERT dbo.MyTable ON;
INSERT INTO dbo.MyTable (ID, MyShortDesc, MyLongDesc)
VALUES(12, 'PASS','Professional Association of SQL Server');
SET IDENTITY_INSERT dbo.MyTable OFF;Установив опцию IDENTITY_INSERT в значение ON, вы можете явно установить значение столбца идентификатора. Однако опцию IDENTITY_INSERT можно включить только для одной таблицы за один раз в сеансе.
Удаление записей из таблицы со столбцом идентификатора
При удалении записи в таблице со столбцом идентификатора значения идентификаторов не повторно используются. Поэтому со временем в значениях столбца идентификатора могут возникать пробелы на основе удаленных записей.
Заключение
Использование столбцов идентификаторов в SQL Server позволяет автоматически генерировать замещающие ключи. Это упрощает процесс создания уникальных значений столбца идентификатора для каждой строки. При проектировании таблицы рассмотрите возможность использования столбца идентификатора в качестве замещающего ключа.