【楽しく学ぶSQL入門】第11章：テーブル

システム開発において、データを効率よく安全に管理するためには、適切な構造を持ったテーブルの設計が不可欠です。本章では、テーブルの作成から変更、削除に至るDDL（データ定義言語）の基本構造について見ていきましょう。

テーブル

データベース内に新しい実体として定義される表のまとまり、それがテーブルです。用途に応じて列を組み合わせ、システム開発現場に必要なデータ構造を定義していきます。

テーブルの作成

データベースに対して新しいテーブルを定義・構築する際に使用するのが、CREATE TABLE文。列名や対応するデータ型、格納時のルールとなる制約を1つずつ組み上げていくのが基本手順です。

CREATE TABLE 表名 (
    列名 データ型 [制約 DEFAULT値など],
    列名 データ型 [制約 DEFAULT値など],
    ...
);

列に対してDEFAULTオプションを付与することによって、データ挿入時に値が省略された場合、あらかじめ決めたデフォルトの値を自動的に指定することができます。システム開発現場における入力漏れを防ぐ実用的な設定です DEFAULTオプションの割り当てには、固定の文字列や数値といったリテラルのほか、式やSYSDATE等のSQL関数をセットすることもできます。

CREATE TABLE income_and_expenditure (
    id INT NOT NULL AUTO_INCREMENT,
    expense VARCHAR(13),
    income_and_expenditure_date DATE,
    income INT DEFAULT 0,
    expenditure INT DEFAULT 0,
    comment VARCHAR(100),
    PRIMARY KEY (id)
);

データ型直後にあるカッコ内の数値は、格納できる許容量（バイト数や桁数）を示します。また、テーブルの列にはデータ整合性を守るための制約指定することも可能。NOT NULL制約は上記の通り列ごとに定義を行い、指定なしの列にはNULLをセットすることが許容される仕組みです。

PRIMARY KEY（主キー）制約は、指定した列の値によって行が一意に限定され（UNIQUE KEY制約）、なおかつNULLの格納を一切認めない（NOT NULL制約）という2つの重要な役割を合わせ持つ強力な制約です。

最後に注意点として、同じデータベースユーザーが同一名のテーブルを重複して作成することはシステム仕様上認められません。実行を試みるとエラーが発生します。

テーブルの削除

不要になったテーブルを構造ごとデータベース内から完全に消去する際には、DROP TABLE文を実行します。

DROP TABLE 表名;

テーブル削除を行う状況において、エラーを誘発する明確なパターンが1つ存在します。それがFOREIGN KEYによる参照整合性制約。他のテーブルから参照されている状態で親の表を消すことはシステム上不可。このエラーを回避するためには、関連する制約をあらかじめ解除するか、依存している子テーブルを先に処理する手順の構築が不可欠です。

参考サイト：MySQL 公式サイト（DROP TABLE 構文）

テーブル名の変更

構築済みのテーブル名称を別の名前に変更したい場合は、ALTER TABLE文にRENAME句を組み合わせて発行します。

ALTER TABLE 元の表名 RENAME 新しい表名;

テーブルの変更（列の追加）

既存のテーブル構造に新たなデータ項目を付け足したいときは、ALTER TABLE文に対してADDコマンドを指定します。

ALTER TABLE 表名 ADD 列名 データ型 [制約 DEFAULT値など], ...;

列を追加する際、DEFAULTオプションをあえて指定しなかった場合、既存レコードの追加列にはすべてNULLが初期値としてセットされます。この新規追加された列は、必ず既存テーブルの定義の最後尾に配置される仕組みです。

テーブルの変更（列の変更）

定義済みの列に対して名称や属性を変更する場合は、目的によってCHANGE句、またはMODIFY句を使い分ける必要があります。

・列名そのものをリネームしたいとき：

ALTER TABLE 表名 CHANGE 元の列名 新しい列名 データ型 [制約 DEFAULT値など];

・列名はそのままで、データ型や制約の特性をアップデートしたいとき：

ALTER TABLE 表名 MODIFY 列名 データ型 [制約 DEFAULT値など], ...;

既存テーブルに対して列の変更をかける際には、データベースのデータ破損を防ぐための厳格な制限事項が存在します。事前に内容を精査しておかなければ実行失敗の原因となります。仕様を網羅しておきましょう。

• 列の最大サイズ（許容バイト数）を拡張・拡大する処理は、データ状態に関わらず常に実行可能。
• 列の許容サイズを縮小・削減したい場合は、対象テーブルのレコード数が完全にゼロであるか、あるいは変更対象列のデータがすべてNULLの状態で埋まっていることが前提条件。
• 新たなNOT NULL制約を途中から追加する際は、テーブルにデータが一切格納されていないか、既存の該当列データ内にNULL値が一箇所も残存していない状態が必須。
• MODIFY句の単体の指定によって直接変更・操作が可能な制約属性は、システム特性上NOT NULL制約のみ。
• 列のDEFAULT値を途中で変更した場合、その変更内容が有効化されるのは「今後のデータ挿入時（INSERT）」から。既存データへの遡及適用はなし。

参考サイト：MySQL 公式サイト（ALTER TABLE 構文）

テーブルの変更（列の削除）

テーブル内から不要な列項目そのものを除去したい場合は、ALTER TABLE文にDROP句を指定します。

ALTER TABLE 表名 DROP 列名;

データ構造を消し去る強力なコマンドであるため、列の削除機能にもいくつかの明確な制約が存在します。不用意な実行はトラブルの元となるため、注意してください。

• 1度のSQL文発行手続きにおいて、個別に削除指定ができるのは1列のみ。
• テーブル構造を維持する仕様上、内部に存在する「最後の1列」を削除してゼロにすることは不可。
• 1度削除コマンドを完遂した構造およびデータは、後方からの復元（ロールバック）不可。(DDLの暗黙のコミット)
• 削除対象の列にデータが格納されていても、削除は可能。

制約の変更（NOT NULL 制約は除く）

すでに設定されている制約（主キーや外部キーなど）を後から変更する場合、直接上書きするコマンドは存在しません。一度既存の制約を明示的に削除し、その後に新定義を追加する二段階の手順を踏む必要があります。さらに、テーブル内にレコードが存在する場合には、格納済みの実データが新しい制約ルールをクリアしていないとエラーが返るため、事前の精査が肝心です。

制約の追加や削除を実施する場合も、列操作と同様にADDやDROPのコマンドをALTER TABLEに組み合わせて発行します。

ALTER TABLE 表名 ADD 列名 型 制約;

ALTER TABLE 表名 DROP 列名 型 制約;

既存テーブルからのテーブル作成

テーブルを作成するアプローチには、ゼロから真っさらに定義する方法だけでなく、既存のテーブルに格納されているデータを抽出してそのまま新しい表として複製・新設する便利な手法が存在します。

① 例）既存データの中から、支出があるデータに合致したレコードをベースに、別名で新規テーブルを構築します。

CREATE TABLE test AS SELECT * FROM income_and_expenditure WHERE expenditure > 0;

この記述内に組み込むSELECT文には、通常のデータ検索で使用するクエリ構文をそのまま流用できます。もちろん、複数のテーブルを結合して抽出した複雑な結果から新しい表を作成することも可能。

（参考）作成の副次的な効果として、選択した各列に対して新しい別名を割り当てて定義することも可能です。また元のテーブルに入っていた実データの値はそのまま新テーブルへ反映されます。ただし、構造上のルールである「制約」に関しては、唯一NOT NULL制約のみが引き継がれる仕様です。主キー（PRIMARY KEY）や外部キーなどの他の各種制約については、表の作成が完了した後に、改めて個別に手動で追加定義を施す必要がある点に留意してください。

参考サイト：MySQL 公式サイト（CREATE TABLE … SELECT 構文）