背景
主キーが存在しないテーブルに後からauto incrementなIDカラムを追加して主キーとするための方法を考えてみました。例えば、変更の追跡をテーブルに設定するためには、そのテーブルに主キーが存在する必要があります。このように、主キーが存在しない既存のテーブルに対して何らかの理由で主キーを追加したくなった場合に、どのような手順で実施するのが良いか、一例をご紹介します。
元テーブル
column1/column2/column3の3カラム構成の[Table]に、デフォルト制約が1つと、インデックスが1つ作成されているとします。
-- 元テーブル
CREATE TABLE [dbo].[Table]
(
[column1] [int] NOT NULL,
[column2] [int] NOT NULL,
[column3] [int] NULL
) ON [PRIMARY]
GO
-- 制約
ALTER TABLE [dbo].[Table] ADD CONSTRAINT [DF_Table_column3] DEFAULT ((0)) FOR [column3]
GO
-- インデックス
CREATE CLUSTERED INDEX [IX_Table] ON [dbo].[Table]
(
[column1] ASC
)
GO
IDカラム追加とPK化クエリ
-- 新テーブルの作成
CREATE TABLE [dbo].[Table_New]
(
[column_id] [bigint] identity NOT NULL,
[column1] [int] NOT NULL,
[column2] [int] NOT NULL,
[column3] [int] NULL
) ON [PRIMARY]
GO
-- 制約
ALTER TABLE [dbo].[Table_New] ADD CONSTRAINT [DF_Table_column3_New] DEFAULT ((0)) FOR [column3]
GO
--ここで、[Table]へのデータ更新が一切ない状況をつくる
-- 新テーブルにデータを投入。IDカラムも自動で採番されつつINSERTされる。
INSERT INTO [Table_New] ([column1], [column2], [column3]) SELECT * FROM [Table]
GO
-- PK 作成
ALTER TABLE [dbo].[Table_New] ADD CONSTRAINT [PK_Table] PRIMARY KEY NONCLUSTERED ([column_id])
GO
-- 既存インデックス作成
CREATE CLUSTERED INDEX [IX_Table] ON [dbo].[Table_New]
(
[column1] ASC
)
GO
-- データ確認
SELECT * FROM [Table]
EXCEPT
SELECT [column1], [column2], [column3] FROM [Table_New]
SELECT [column1], [column2], [column3] FROM [Table_New]
EXCEPT
SELECT * FROM [Table]
-- テーブルのリネーム
EXEC sp_rename N'Table', N'Table_Old'
EXEC sp_rename N'DF_Table_column3', N'DF_Table_column3_Old'
GO
EXEC sp_rename N'Table_New', N'Table'
EXEC sp_rename N'DF_Table_column3_New', N'DF_Table_column3'
GO
--ここで、[Table]へのデータ更新が通常通り行われる状況に戻す
--万一何か起きた時に戻す用クエリ
/*
EXEC sp_rename N'Table', N'Table_New'
EXEC sp_rename N'DF_Table_column3', N'DF_Table_column3_New'
GO
EXEC sp_rename N'Table_Old', N'Table'
EXEC sp_rename N'DF_Table_column3_Old', N'DF_Table_column3'
GO
*/
リネーム以外は事前にリハーサルできるので所要時間をほぼ正確に事前に見積もることもできます。
注意点
データ量が多い場合はトランザクションログの容量を使い切らないように注意が必要です。（自動拡張OFFだと更新できなくなりますし、ONだと自動拡張が走ったときに一時的に更新がストップする可能性があるため）