SQL Server Tips - 制約やテーブルカラムの追加/削除クエリ

はじめに

「DBの制約やカラムの追加・削除を後から行う時、クエリはどう書くんだっけ...」という時のためのメモ。
アプリのバージョンアップなどにて、インストーラー内にクエリを組み込む際に、どのアプリバージョンからのアップデートでも適用でき、クエリ自体へのメンテ工数が少なくなる ことが実現できる観点で作成。
対象は SQL Server を想定。

テーブルカラム

既存のテーブルに対して、新しいカラムを追加または削除する。
ポイントは、sys.objectsとsys.columnsを INNER JOIN でテーブル結合し、カラム検索を行っている部分。
IF EXISTSおよびIF NOT EXISTSを使用して、カラムの有無を判断している。

カラム追加

IF NOT EXISTS (
    SELECT
        c.name
    FROM
        sys.objects o
        INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.type = N'U'
        AND o.name = N'<カラムを追加するテーブル名>'
        AND c.name = N'<追加したいカラム名>'
)
ALTER TABLE <カラムを追加するテーブル名> ADD <追加したいカラム名> <データ型> [ NULL / NOT NULL ]

カラム追加(例)

IF NOT EXISTS (
    SELECT
        c.name
    FROM
        sys.objects o
        INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.type = N'U'
        AND o.name = N'EVENTS'
        AND c.name = N'ACTION'
)
ALTER TABLE [dbo].[EVENTS] ADD [ACTION] nvarchar(256) NOT NULL

カラム削除は、テーブルから列を削除することで、列および列に含まれているすべてのデータも削除される。

カラム削除

IF EXISTS (
    SELECT
        c.name
    FROM
        sys.objects o
        INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.type = N'U'
        AND o.name = N'<カラムを追加するテーブル名>'
        AND c.name = N'<追加したいカラム名>'
)
ALTER TABLE <削除対象のカラムが存在するテーブル名> DROP COLUMN <追削除したいカラム名>

カラム削除(例)

IF EXISTS (
    SELECT
        c.name
    FROM
        sys.objects o
        INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.type = N'U'
        AND o.name = N'EVENTS'
        AND c.name = N'ACTION'
)
ALTER TABLE [dbo].[EVENTS] DROP COLUMN [ACTION]

外部キー

既存のカラムに対して、新しく外部キー制約(FK)を追加または削除する。
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSを参照し、制約名CONSTRAINT_NAMEで同じ制約名の外部キー(FK)が存在していないか確認する。

FKの追加

IF NOT EXISTS (
    SELECT
        *
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    WHERE [CONSTRAINT_NAME] = N'<FK制約名>'
)
ALTER TABLE <外部キーを追加するテーブル名>
    ADD CONSTRAINT <FK制約名> FOREIGN KEY <外部キーとなるカラム名>
        REFERENCES <FK参照元のテーブル名> (<FK参照元のテーブルカラム名>)
        [ ON UPDATE CASCADE ]
        [ ON DELETE CASCADE ]

FKの追加(例)

IF NOT EXISTS (
    SELECT
        *
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    WHERE [CONSTRAINT_NAME] = N'FK_colname'
)
ALTER TABLE [dbo].[fk_table]
    ADD CONSTRAINT N'FK_colname' FOREIGN KEY (colname)
        REFERENCES [dbo].[pk_table] (colname)

FKの削除

IF NOT EXISTS (
    SELECT
        *
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    WHERE [CONSTRAINT_NAME] = N'<FK制約名>'
)
ALTER TABLE <外部キーを削除するテーブル名>
    DROP CONSTRAINT <FK制約名>

FKの削除(例)

IF NOT EXISTS (
    SELECT
        *
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    WHERE [CONSTRAINT_NAME] = N'FK_colname'
)
ALTER TABLE [dbo].[fk_table]
    DROP CONSTRAINT N'FK_colname'

OBJECT_IDを使用した制約確認

ISO 標準定義の INFORMATION_SCHEMA だけでなく、SQL Server では、メタデータ関数を使用することも可能。
OBJECT_IDを使用して、制約を確認することもできる。エラーが発生した場合は NULL が返される。

OBJECT_IDを使用した外部キー制約削除(例)

IF OBJECT_ID(N'dbo.[FK_colname]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[fk_table] DROP CONSTRAINT N'FK_colname'

-- 制約があるかの存在確認
SELECT OBJECT_ID(N'dbo.[FK_colname]', 'F')

制約名の前後に角カッコ [] を入れているのは、偽陰性を防ぐためのもの。
また、この2つ目のN'F'は省略可能なパラメータ。以下のような指定を行うことができるようになっている。

• C: CHECK 制約
• D: DEFAULT (制約またはスタンドアロン)
• F: FOREIGN KEY 制約
• PK: PRIMARY KEY 制約
• UQ: UNIQUE 制約

また、master データベースで実行している場合は、データベース名が必要になったりする。詳しくは後述の Microsoft Docs を参照。

参考情報

Microsoft Docs

• テーブルへの列の追加 (データベース エンジン)
• テーブルからの列の削除
• 外部キーのリレーションシップの作成
• 外部キーのリレーションシップの削除
• OBJECT_ID (Transact-SQL)
• メタデータ表示の構成