Добавление идентификатора к существующему столбцу

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

Чтобы переименовать существующий столбец, используйте команду sp_RENAME:

EXEC sp_RENAME 'Имя_Таблицы.Существующее_Имя_Столбца', 'Новое_Имя_Столбца', 'COLUMN'

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

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

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

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

ALTER TABLE ИмяТаблицы ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Пример установки первичного ключа:

Созданный новый столбец будет называться UserID:

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

После этого удалите переименованный столбец:

ALTER TABLE Имя_Таблицы DROP COLUMN Переименованный_Имя_Столбца

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

ALTER TABLE Users DROP COLUMN OldUserID

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

Я разработчик на Java и попал в команду, где нет администратора базы данных (DBA), и мне не предоставили права DBA. Мне поручили переместить всю схему между двумя базами данных, и поскольку у меня не было доступа к GUI в SQL Server 2008, я должен был реализовать это, запуская скрипты.

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

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

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

exec sp_help 'dbo.table_name_old';

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

3. Отключите инкремент идентификатора для копирования данных.

SET IDENTITY_INSERT dbo.table_name ON 

4. Перенесите данные.

INSERT INTO dbo.table_name_new
(
field1, field2, и т.д...
)
SELECT 
field1, field2, и т.д...
FROM 
dbo.table_name_old;

5. Проверьте, что данные скопированы.

SELECT * FROM dbo.table_name_new

6. Включите инкремент идентификатора.

SET IDENTITY_INSERT dbo.table_name_new OFF

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

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. Убедитесь, что у вас есть все скрипты для первичных и внешних ключей всех связанных таблиц перед следующим шагом.

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

10. Удалите внешние ключи из зависимых таблиц с использованием следующего синтаксиса:

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

11. Удалите оригинальную таблицу:

DROP TABLE dbo.table_name_old;

12. Следующие шаги зависят от скриптов, которые вы создали на шаге 9 в SQL Server 2008.

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

-- Добавьте внешние ключи в новую таблицу.

-- Верните внешние ключи в зависимую таблицу.

13. Убедитесь, что все корректно и завершено. Я воспользовался GUI, чтобы проверить таблицы.

14. Переименуйте новую таблицу в имя оригинальной таблицы.

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

В результате все сработало!

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

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

Чтобы достичь этого, нужно выполнить несколько шагов следующим образом:

Предположим, что TableName = 'Employee' и ColumnName = 'EmployeeId'.

1. Добавьте новый столбец 'EmployeeId_new' в таблицу 'Employee':

   ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1);
   

2. Теперь удалите столбец 'EmployeeId' из таблицы 'Employee':

   ALTER TABLE Employee DROP COLUMN EmployeeId;
   

   Это вызовет ошибку из-за правил ограничения первичного ключа, которые применимы и проверяют структуру столбца.

   Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on column [EmployeeId].
   

3. Поэтому сначала нужно удалить ограничение первичного ключа из таблицы 'Employee', а потом мы сможем удалить столбец:

   ALTER TABLE Employee DROP CONSTRAINT [PK_dbo.Employee];
   

4. Теперь мы можем удалить столбец 'EmployeeId' из таблицы 'Employee', как это было в предыдущем шаге, где мы получили ошибку:

   ALTER TABLE Employee DROP COLUMN EmployeeId;
   

5. Теперь столбец 'EmployeeId' удален из таблицы. Мы переименуем только что добавленный новый столбец 'EmployeeId_new' в 'EmployeeId':

   EXEC sp_rename 'Employee.EmployeeId_new', 'EmployeeId', 'COLUMN';
   

6. Чтобы вернуть таблицу в такой же вид, как она была, мы должны снова добавить ограничение первичного ключа для столбца 'EmployeeId':

   ALTER TABLE Employee ADD CONSTRAINT [PK_dbo.Employee] PRIMARY KEY (EmployeeId);
   

Теперь таблица 'Employee' с 'EmployeeId' модифицирована в соответствии с правилами Identity и с существующим ограничением первичного ключа.

Вы не можете сделать это таким образом. Чтобы установить свойство идентичности в SQL Server, вам нужно добавить новый столбец, удалить оригинальный столбец и переименовать новый столбец. Либо вы можете создать новую таблицу, скопировать данные в неё, удалить старую таблицу и затем переименовать новую таблицу, чтобы она соответствовала старой.

Если вы используете SSMS и устанавливаете свойство идентичности на ВКЛ в дизайнере, вот что происходит за кулисами в SQL Server. Например, если у вас есть таблица с именем [user], и вы хотите сделать столбец UserID идентичным, выполнится следующий процесс:

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, MiddleInitial FROM 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

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