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

# はじめに

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

# テーブルカラム

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

```sql:カラム追加
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 ]
```
```sql:カラム追加(例)
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
```

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

```sql:カラム削除
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 <追削除したいカラム名>
```
```sql:カラム削除(例)
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)が存在していないか確認する。

```sql: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 ]
```
```sql: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)
```
```sql:FKの削除
IF NOT EXISTS (
    SELECT
        *
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    WHERE [CONSTRAINT_NAME] = N'<FK制約名>'
)
ALTER TABLE <外部キーを削除するテーブル名>
    DROP CONSTRAINT <FK制約名>
```
```sql: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 が返される。

```sql: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

- [テーブルへの列の追加 (データベース エンジン)](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-ver15)
- [テーブルからの列の削除](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/delete-columns-from-a-table?view=sql-server-ver15)
- [外部キーのリレーションシップの作成](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15)
- [外部キーのリレーションシップの削除](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/delete-foreign-key-relationships?view=sql-server-ver15)
- [OBJECT_ID (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/object-id-transact-sql?view=sql-server-ver15)
- [メタデータ表示の構成](https://docs.microsoft.com/ja-jp/sql/relational-databases/security/metadata-visibility-configuration?view=sql-server-ver15)