Identity sql что это

При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

CREATE TABLE Customers ( Id INT PRIMARY KEY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )

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

Установка первичного ключа на уровне таблицы:

CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), PRIMARY KEY(Id) )

Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:

CREATE TABLE OrderLines ( OrderId INT, ProductId INT, Quantity INT, Price MONEY, PRIMARY KEY(OrderId, ProductId) )

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

IDENTITY

Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )

Также можно использовать полную форму атрибута:

IDENTITY(seed, increment)

Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:

IDENTITY(1, 1)

То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:

Id INT IDENTITY (2, 3)

В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая — 5, третья — 8 и т.д.

Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.

UNIQUE

Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE .

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )

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

Также мы можем определить этот атрибут на уровне таблицы:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), UNIQUE(Email, Phone) )

NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа — в этом случае по умолчанию столбец имеет значение NOT NULL.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )

DEFAULT

Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE );

Здесь для столбца Age предусмотрено значение по умолчанию 18.

CHECK

Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE CHECK(Email !=''), Phone VARCHAR(20) UNIQUE CHECK(Phone !='') );

Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также с помощью CHECK можно создать ограничение в целом для таблицы:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) )

Оператор CONSTRAINT. Установка имени ограничений.

С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK.

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

CREATE TABLE Customers ( Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE, Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE )

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

"PK_" - для PRIMARY KEY
"FK_" - для FOREIGN KEY
"CK_" - для CHECK
"UQ_" - для UNIQUE
"DF_" - для DEFAULT

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

И также можно задать все имена ограничений через атрибуты таблицы:

CREATE TABLE Customers ( Id INT IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30), Phone VARCHAR(20), CONSTRAINT PK_Customer_Id PRIMARY KEY (Id), CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), CONSTRAINT UQ_Customer_Email UNIQUE (Email), CONSTRAINT UQ_Customer_Phone UNIQUE (Phone) )

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

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

Ниже приводится пример создания таблицы Printer_Inc с автоинкрементируемым столбцом (code) в MS Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server.

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

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

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

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

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

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

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

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

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

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

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

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

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

Страницы:

IDENTITY (функция) (Transact-SQL)

Используется только в инструкции SELECT с предложением INTO table для вставки столбца идентификаторов в новую таблицу. Хотя они похожи, функция IDENTITY не является свойством IDENTITY, которое используется с инструкциями CREATE TABLE и ALTER TABLE.

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

Синтаксис

IDENTITY (data_type [ , seed , increment ] ) AS column_name

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

data_type
Тип данных столбца идентификаторов. Для столбца идентификаторов допустимы любые целочисленные типы данных, за исключением типов bit и decimal.

seed
Целочисленное значение, присваиваемое первой строке таблицы. Каждой последующей строке присваивается следующее значение идентификатора, равное последнему значению IDENTITY, увеличенному на значение increment. Если не указан ни аргумент seed, ни аргумент increment, то значения по умолчанию обоих равны 1.

increment
Целочисленное значение, добавляемое к значению seed для каждой последующей строки таблицы.

column_name
Имя столбца, который вставляется в новую таблицу.

Типы возвращаемых данных

Возвращает тот же тип, что и аргумент data_type.

Замечания

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

--(1) SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable; --(2) SELECT ID_Num = IDENTITY(int, 1, 1) INTO NewTable FROM OldTable;

Примеры

Следующий пример вставляет все строки из таблицы из Contact AdventureWorks2022database в новую таблицу с именем NewContact . Функция IDENTITY используется, чтобы начать в таблице NewContact отсчет идентификационных номеров с 100 вместо 1.

USE AdventureWorks2022; GO IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL DROP TABLE Person.NewContact; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT IDENTITY(smallint, 100, 1) AS ContactNum, FirstName AS First, LastName AS Last INTO Person.NewContact FROM Person.Person; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO SELECT ContactNum, First, Last FROM Person.NewContact; GO

CREATE TABLE (Transact-SQL) IDENTITY (Свойство)

Создает в таблице столбец идентификаторов. Это свойство указывается в инструкциях языка Transact-SQL CREATE TABLE и ALTER TABLE.

Свойство IDENTITY отличается от свойства SQL-DMO Identity , которое предоставляет свойство удостоверения строки столбца.

Синтаксис

IDENTITY [ (seed , increment) ]

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Аргументы

seed

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

increment

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

В Azure Synapse Analytics значения для удостоверений не являются добавочными из-за распределенной архитектуры хранилища данных. Дополнительные сведения см. в статье "Использование IDENTITY" для создания суррогатных ключей в пуле SQL Synapse.

Необходимо указывать либо оба аргумента (и seed, и increment), либо не указывать ни одного из них. Если ничего не указано, применяется значение по умолчанию (1,1).

Замечания

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

Каждое новое значение создается на основе текущего начального и добавочного значения.
Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.

Свойство identity в столбце не гарантирует следующие условия:

Уникальность значения

Примечание. Azure Synapse Analytics не поддерживает PRIMARY KEY или ограничивает или UNIQUE UNIQUE индекс. Дополнительные сведения см. в статье "Использование IDENTITY" для создания суррогатных ключей в пуле SQL Synapse.

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

Если таблица со столбцом идентификаторов опубликована для репликации, этот столбец должен обслуживаться в соответствии с типом репликации. Дополнительные сведения см. в статье Репликация столбцов идентификаторов.

Для каждой таблицы можно создать только один столбец идентификаторов.

В таблицах, оптимизированных для памяти, начальное и добавочное значение должны быть заданы 1, 1 . Задание начального или добавочного значения, отличного 1 от следующей ошибки: The use of seed and increment values other than 1 is not supported with memory optimized tables

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

Примеры

А. Использование свойства IDENTITY с CREATE TABLE

В следующем примере производится создание новой таблицы со свойством IDENTITY для получения автоматически увеличивающегося идентификационного номера.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL DROP TABLE new_employees; GO CREATE TABLE new_employees ( id_num INT IDENTITY(1, 1), fname VARCHAR(20), minit CHAR(1), lname VARCHAR(30) ); INSERT new_employees (fname, minit, lname) VALUES ('Karin', 'F', 'Josephs'); INSERT new_employees (fname, minit, lname) VALUES ('Pirkko', 'O', 'Koskitalo');

B. Использование универсального синтаксиса для поиска пробелов в значениях удостоверений

Следующий пример демонстрирует базовый синтаксис для поиска разрывов в нумерации идентификаторов, возникающих при удалении данных.

Первая часть данного скрипта Transact-SQL приведена только в учебных целях. Вы можете запустить скрипт Transact-SQL, который начинается с комментария: -- Create the img table .

-- Here is the generic syntax for finding identity value gaps in data. -- The illustrative example starts here. SET IDENTITY_INSERT tablename ON; DECLARE @minidentval column_type; DECLARE @maxidentval column_type; DECLARE @nextidentval column_type; SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY) FROM tablename IF @minidentval = IDENT_SEED('tablename') SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename') FROM tablename t1 WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND @maxidentval AND NOT EXISTS ( SELECT * FROM tablename t2 WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename') ) ELSE SELECT @nextidentval = IDENT_SEED('tablename'); SET IDENTITY_INSERT tablename OFF; -- Here is an example to find gaps in the actual data. -- The table is called img and has two columns: the first column -- called id_num, which is an increasing identification number, and the -- second column called company_name. -- This is the end of the illustration example. -- Create the img table. -- If the img table already exists, drop it. -- Create the img table. IF OBJECT_ID('dbo.img', 'U') IS NOT NULL DROP TABLE img; GO CREATE TABLE img ( id_num INT IDENTITY(1, 1), company_name SYSNAME ); INSERT img (company_name) VALUES ('New Moon Books'); INSERT img (company_name) VALUES ('Lucerne Publishing'); -- SET IDENTITY_INSERT ON and use in img table. SET IDENTITY_INSERT img ON; DECLARE @minidentval SMALLINT; DECLARE @nextidentval SMALLINT; SELECT @minidentval = MIN($IDENTITY) FROM img IF @minidentval = IDENT_SEED('img') SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img') FROM img t1 WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND NOT EXISTS ( SELECT * FROM img t2 WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img') ) ELSE SELECT @nextidentval = IDENT_SEED('img'); SET IDENTITY_INSERT img OFF;

Связанный контент

Инструкция ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_INCR (Transact-SQL)
@@IDENTITY (Transact-SQL)
IDENTITY (Function) (Transact-SQL)
IDENT_SEED (Transact-SQL)
SELECT (Transact-SQL)
SET IDENTITY_INSERT (Transact-SQL)
Репликация столбцов идентификаторов

Обратная связь

Были ли сведения на этой странице полезными?

Identity sql что это