SQL ALTER TABLEのサンプルSQL データベース製品によって、機能拡張が異なる
  • facebookページ
  • twitterページ
  • 2020.02.10

    SQL ALTER TABLEのサンプルSQL データベース製品によって、機能拡張が異なる

    SQLのalter tableについてまとめています。

    ALTER TABLEは、データベーステーブルの構造を変更するためのDDL(データ定義言語)。SQL規格のSQL92にて定義されている標準SQLです。

    ALTER TABLEの構文

    SQL92によると、ALTER TABLEの規格は以下のように定義されています。

    指定可能なアクションは以下の通りです。

    • カラム追加定義 (ADD [ COLUMN ] <column definition>)
    • カラム変更定義  ( ALTER [ COLUMN ] <column name> <alter column action> )
    • カラム削除(drop)定義 (<drop column default clause> 、 DROP [ COLUMN ] <column name> <drop behavior> )
    • テーブルの制約を追加 ( ADD <table constraint definition> )
    • テーブルの制約をdrop ( DROP CONSTRAINT <constraint name> <drop behavior> )

    なお、指定するテーブル名は、ベーステーブルでかつ、ローカル一時テーブルではないことが条件となります。

    SQL ServerのALTER TABLEの拡張

    参考)ALTER TABLE (Transact-SQL) – SQL Server | Microsoft Docs

    SQLServerのテーブルには、ディスクベーステーブルとメモリ最適化テーブルがあり、それぞれALTER TABLEの構文が異なります。

    また、規格のアクション以外の拡張でパーティションの再割当てと再構築、トリガーの無効化、有効化が可能です。

    MySQLのALTER TABLEの拡張

    参考)MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

    MySQLでは、規格のアクション以外に、再パーティション化、パーティション追加、削除、マージ、分割、パーティションの保守の実行が可能です。

    MySQLでALTER TABLEを実行すると、元のテーブルの一時的なコピーを作成し、ALTERで指定したテーブルの処理の完了を待ってから、コピーに変更を適用し、元のテーブルを削除後にコピーしたテーブルをリネームします。(一部の例外あり)

    PostgreSQLのALTER TABLEの拡張

    参考)PostgreSQL 11.5文書 ALTER TABLE

    PostgreSQLでは、規格のアクション以外にテーブルのリネーム、テーブルスペースの割り当て、パーティションのアタッチ・デタッチが可能です。さらに、トリガーの有効化・無効化、リライトルール、ロウのセキュリティレベル設定、クラスタの設定などが可能です。

    OracleのALTER TABLE

    参考)Oracle R19 SQL Language Reference  ALTER TABLE

    Oracleでは、規格のアクション以外にパーティション操作や、トリガーの追加、削除が可能です。

    ALTER TABLEのサンプルSQL

    実際にALTER TABLEを使ってテーブルを変更してみましょう。

    サンプルデータベースとしてEmployeesを使い、MySQLのバージョンは8.0.18を前提としています。

    【関連記事】
    MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的

    まずは、データベースのテーブルemployeesと同じ構造のemployees2を作成します。

    カラム追加は、alter table テーブル名 add

    employees2テーブルの、hire_dateの後ろにcol1というカラムを追加してみましょう。col1のデータタイプはvarchar(10)です。

    col1が追加されました。しかし、NULL許可が「YES」になっています。MySQLの場合、何も指定しない場合、NULL許可はデフォルトでYESになるんですね。

    カラム変更は、alter table テーブル名 modifyかchange

    カラムcol1のNULL許可をNOにしてみましょう。

    変更できました。

    MySQLには、リネームと設定を同時に行う「change」という拡張があります。

    以下は、employees2テーブルのcol1をcol2にリネームし、データタイプをvarchar(20)、NULL許可をNOに変更します。

    MySQLでは、changeでカラム名をリネームすると、NULL許可がデフォルトのYESに変わってしまうため上記のように指定しています。

    カラム削除は、alter table テーブル名 drop

    employees2テーブルから、カラムcol2を削除(drop)してみましょう。

    col2が削除されたことが確認できました。

    まとめ

    ポテパンダの一言メモ
    • ALTER TABLEは、SQL92規格の標準SQL
    • アクションを指定することで、テーブルのカラム追加、変更、削除と制約の追加、削除が可能
    • データベース製品によって、ALTER TABLEの機能が拡張されているケース有り


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE