столбец - как добавить identity sql




Добавление идентификатора в существующий столбец (13)

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

У меня есть сценарий для очистки идентификаторов, чтобы они были последовательно, начиная с 1, отлично работает в моей тестовой базе данных.

Что такое команда SQL для изменения столбца, чтобы иметь свойство идентификации?


IN sql server 2014 (я не знаю о более низких версиях), вы можете сделать это просто, используя последовательность.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

Отсюда: Последовательность как значение по умолчанию для столбца


В SQL 2005 и выше существует трюк для решения этой проблемы без изменения страниц данных таблицы. Это важно для больших таблиц, где касание каждой страницы данных может занимать минуты или часы. Трюк также работает, даже если столбец идентификатора является первичным ключом, является частью кластерного или некластеризованного индекса или других ошибок, которые могут отключить более простое решение «добавить / удалить / переименовать столбец».

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

Обычно ALTER TABLE ... SWITCH используется для эффективной замены полного раздела в многораздельной таблице новым пустым разделом. Но он также может использоваться и в несегментированных таблицах.

Я использовал этот трюк для преобразования менее чем за 5 секунд столбца таблицы из 2,5 миллиарда строк из IDENTITY в не-идентификатор (для запуска многочасового запроса, чей план запросов работал лучше для не-IDENTITY столбцы), а затем восстановили настройку IDENTITY, снова менее чем за 5 секунд.

Вот пример кода, как это работает.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

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

  • Насколько мне известно, идентичность - это единственное, что можно изменить в столбцах таблицы с помощью этого метода. Добавление / удаление столбцов, изменение нулевой и т. Д. Недопустимы.
  • Вам нужно будет отбросить ключи foriegn до того, как вы включите коммутатор и восстановите их после.
  • То же самое для функций SCHEMABINDING, просмотров и т. Д.
  • индексы новых таблиц должны точно совпадать (одинаковые столбцы, одинаковый порядок и т. д.),
  • Старые и новые таблицы должны быть в одной файловой группе.
  • Работает только на SQL Server 2005 или более поздней версии
  • Ранее я считал, что этот трюк работает только на выпусках Enterprise или Developer SQL Server (поскольку разделы поддерживаются только в версиях Enterprise и Developer), но Мейсон Г. Жвити в своем комментарии ниже говорит, что он также работает и в SQL Standard Edition. Я предполагаю, что это означает, что ограничение на Enterprise или Developer не распространяется на ALTER TABLE ... SWITCH.

В TechNet есть хорошая статья, в которой подробно описаны требования, изложенные выше.

UPDATE - У Эрика У было комментарий ниже, который добавляет важную информацию об этом решении. Копируем его здесь, чтобы удостовериться, что он получает больше внимания:

Здесь есть еще одна оговорка, о которой стоит упомянуть. Несмотря на то, что новая таблица с радостью получит данные из старой таблицы, а все новые строки будут вставлены после шаблона идентификации, они будут начинаться с 1 и потенциально прерываться, если указанный столбец является первичным ключом. DBCC CHECKIDENT('<newTableName>') запустить DBCC CHECKIDENT('<newTableName>') сразу после переключения. Дополнительную msdn.microsoft.com/en-us/library/ms176057.aspx см. В разделе msdn.microsoft.com/en-us/library/ms176057.aspx .

Если таблица активно расширяется с помощью новых строк (это означает, что у вас нет большого количества простоя между добавлением IDENTITY и добавлением новых строк, то вместо DBCC CHECKIDENT вам нужно вручную установить значение семенного идентификатора в новой схеме таблицы чтобы быть больше, чем самый большой существующий идентификатор в таблице, например IDENTITY (2435457, 1) . Возможно, вы сможете включить как ALTER TABLE...SWITCH и DBCC CHECKIDENT в транзакции (или нет - не тестировали это), но похоже, что установка начального значения вручную будет проще и безопаснее.

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


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

Поскольку я хочу добавить идентификатор, поэтому он всегда начинается с 1 до Конец записи, который я хочу.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

Это создаст тот же самый первичный столбец с идентификатором

Я использовал эти ссылки: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Добавить первичный ключ в существующую таблицу


Вы не можете изменить столбец как столбец IDENTITY. Что вам нужно сделать, это создать новый столбец, который определяется как идентификатор из get-go, затем удалить старый столбец и переименовать новый в старое имя.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Марк


Если исходный плакат действительно хотел установить существующий столбец как PRIMARY KEY для таблицы и на самом деле не нужен столбец, который будет столбцом IDENTITY (две разные вещи), то это можно сделать с помощью t-SQL с помощью:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Обратите внимание на скобки вокруг имени столбца после опции PRIMARY KEY .

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


Как я понял в обычных случаях, мы создаем таблицу с ключом Primary , обладающим свойством Identity
Поэтому переименовать или удалить столбец, связанный с ограничением первичного ключа , не будет возможным, поскольку правила ограничения проверяют структуру столбцов.
Чтобы достичь этого, мы должны обработать некоторые шаги следующим образом:
Предположим, что TableName = 'Employee' и ColumnName = 'EmployeeId'

1. Добавить столбец «EmployeeId_new» в таблице «Сотрудник»
ALTER TABLE Сотрудник ADD EmployeeId_new INT IDENTITY (1,1)

  1. Теперь удалите столбец «EmployeeId» из таблицы «Сотрудник»
    ALTER TABLE Сотрудник DROP COLUMN EmployeeId

  2. Это вызовет ошибку, поскольку правила ограничения первичного ключа применимы и проверяют структуру столбцов.
    * ### ' Msg 5074, уровень 16, состояние 1, строка 1 Объект [PK_dbo.Employee] зависит от colmn [EmployeeId].' ###

  3. Поэтому нам нужно сначала удалить ограничение первичного ключа из таблицы «Сотрудник», после чего мы можем удалить столбец
    ALTER TABLE Ограничение DROP сотрудника [PK_dbo.Employee]

  4. Теперь мы можем удалить столбец «EmployeeId» из таблицы «Employee», как это было на предыдущем шаге, где мы получили ошибку
    ALTER TABLE Сотрудник DROP COLUMN EmployeeId

  5. Теперь Столбец «EmployeeId» удален из таблицы Итак, мы переименуем новый добавленный столбец «EmployeeId_new» с «EmployeeId»,
    sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

  6. Чтобы переупорядочить таблицу в том же виде, что и было, мы должны добавить ограничение первичного ключа для столбца «EmployeeId»
    ALTER TABLE Сотрудник добавляет ограничение [PK_dbo.Employee] первичный ключ (EmployeeId)

8. Теперь таблица «Сотрудник» с «EmployeeId» изменена для правил Identity вместе с существующим ограничением первичного ключа


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

Если мы используем SQL Server Management Studio, чтобы избавиться от значения идентификатора в столбце «id», создается новая временная таблица, данные перемещаются во временную таблицу, старая таблица удаляется и новая таблица переименовывается.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в дизайнере и выберите «Сгенерировать сценарий изменений».

Вы увидите, что это то, что SQL-сервер делает в фоновом режиме.


Принятый ответ неверен: вы можете изменить существующий столбец как идентификатор, если он не содержит никаких нулевых значений. После изменения семя идентичности начнется с max (column) + 1.

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


Чтобы изменить свойства идентификации для столбца:

  • В проводнике сервера щелкните правой кнопкой мыши таблицу со свойствами идентификации, которые вы хотите изменить, и нажмите «Открыть таблицу определения». Таблица открывается в Table Designer.
  • Снимите флажок Разрешить nulls для столбца, который вы хотите изменить.
  • На вкладке «Свойства столбца» разверните свойство «Спецификация удостоверений».
  • Щелкните ячейку сетки для дочернего объекта Is Identity и выберите «Да» в раскрывающемся списке.
  • Введите значение в ячейке Identity Seed. Это значение будет присвоено первой строке в таблице. Значение 1 будет присвоено по умолчанию.

Вот и все, и это сработало для меня


Щелкните правой кнопкой мыши на имени таблицы в Обозревателе объектов. Вы получите некоторые варианты. Нажмите «Дизайн». Для этой таблицы будет открыта новая вкладка. Вы можете добавить ограничение Identity здесь в «Свойства столбца».


Я разработчик java, который оказался в команде без администратора баз данных, и где я как разработчик, я не могу получить права DBA. Мне было поручено переместить целую схему между двумя базами данных, поэтому без администратора базы данных мне пришлось это сделать и сделать это, запустив скрипты, не имея возможности использовать графический интерфейс в SQL Server 2008, потому что у меня не было прав администратора.

Однако все было перемещено без проблем, однако при запуске хранимой процедуры на новой schema.table я обнаружил, что потерял поле идентификации в таблице. Я дважды проверил скрипт, который создал таблицу, и он был там, однако SQL Server не получил его, когда я запускал скрипт. Позднее я сказал администратору базы данных, что раньше он видел эту проблему.

В любом случае, для SQL Server 2008, это шаги, которые я предпринял, чтобы решить эту проблему, и они работали, поэтому я размещаю это здесь, надеясь, что это поможет кому-то. Это то, что я сделал, поскольку у меня были зависимости FK от другой таблицы, что усложнило это:

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

1.) Найти статистику по таблице:

exec sp_help 'dbo.table_name_old';

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

3.) Отключите личность для перемещения данных.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Передайте данные.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Проверьте данные.

SELECT * FROM dbo.table_name_new

6.) Восстановите идентификатор.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) Это лучший сценарий, который я нашел, чтобы получить все отношения FK, чтобы проверить, какие таблицы (исходники) указаны в качестве зависимостей, и я наткнулся на многие, поэтому это хранитель!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Перед этим следующим шагом убедитесь, что у вас есть все скрипты PK и FK для всех таблиц.

9.) Вы можете щелкнуть правой кнопкой мыши по каждому ключу и создать скрипт с помощью SQL Server 2008

10.) Отбросьте FK (ы) из таблицы (-ов) зависимостей, используя этот синтаксис:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Отбросьте исходную таблицу:

DROP TABLE dbo.table_name_old;

13.) Следующие шаги основаны на сценариях, созданных в SQL Server 2008 на шаге 9.

- Добавить ПК в новую таблицу.

- Добавить FK в новую таблицу.

- Добавить обратно FK в таблицу зависимостей.

14.) Убедитесь, что все правильно и полно. Я использовал графический интерфейс для просмотра таблиц.

15.) Переименуйте новую таблицу в имя исходных таблиц.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Наконец, все сработало!


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

если вы используете SSMS и устанавливаете свойство identity в ON в конструкторе, это то, что SQL Server делает за кулисами. Поэтому, если у вас есть таблица с именем [user], это происходит, если вы делаете идентификатор пользователя и идентификатор

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

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


Простое объяснение

Переименуйте существующий столбец, используя sp_RENAME

EXEC sp_RENAME 'Table_Name.Existing_ColumnName', 'New_ColumnName', 'COLUMN'

Пример для переименования:

Существующий столбец UserID переименован в OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

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

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Пример для Set Primary key

Новое имя созданного столбца - UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

затем опустите переименованную колонку

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Пример для столбца с переименованием Drop

ALTER TABLE Users DROP COLUMN OldUserID

Теперь мы добавили ключевой ключ и идентификатор в существующий столбец таблицы.





identity-column