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

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

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

1 ALTER TABLEの構文
2 ALTER TABLEのサンプルSQL
3 まとめ

ALTER TABLEの構文

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

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を前提としています。

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

mysql> create table employees2 like employees;

mysql> desc employees2;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

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

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

mysql> alter table employees2 add col1 varchar(10) after hire_date;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees2;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| col1       | varchar(10)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

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

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

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

mysql> alter table employees2 modify col1 varchar(10) not null;

mysql> desc employees2;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| col1       | varchar(10)   | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

変更できました。

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

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

mysql> alter table employees2 change col1 col2 varchar(20) not null;

mysql> desc employees2;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| col2       | varchar(20)   | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

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

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

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

mysql> alter table employees2 drop col2;

mysql> desc employees2;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

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

まとめ

ポテパンダの一言メモ

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

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。

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

ALTER TABLEの構文

SQL ServerのALTER TABLEの拡張

MySQLのALTER TABLEの拡張

PostgreSQLのALTER TABLEの拡張

OracleのALTER TABLE

ALTER TABLEのサンプルSQL

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

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

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

まとめ

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

卒業生の多くがWebエンジニアとして活躍

経験豊富なエンジニア陣が直接指導

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

MOVIE
動画カリキュラム

RECOMMEND
おすすめ記事

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

ALTER TABLEの構文

SQL ServerのALTER TABLEの拡張

MySQLのALTER TABLEの拡張

PostgreSQLのALTER TABLEの拡張

OracleのALTER TABLE

ALTER TABLEのサンプルSQL

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

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

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

まとめ

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

卒業生の多くがWebエンジニアとして活躍

経験豊富なエンジニア陣が直接指導

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

MOVIE 動画カリキュラム

RECOMMEND おすすめ記事

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

MOVIE
動画カリキュラム

RECOMMEND
おすすめ記事