テーブル定義の設定
データベース上にテーブルを作成する際に、テーブル定義の設定します。
テーブル定義とは、どんな種類のカラムが用意されているか、カラムに設定している属性やなどテーブルの詳細情報のことです。
この記事では、テーブルを定義するデータ型やオプションや、テーブル定義を確認する方法について解説します。
テーブルを作成する方法については、以下の記事で解説しているので参考までに。
【関連記事】
▶︎MySQLのテーブル作成 既存DBからのDDL生成はエクスポート機能を使う
▶︎PostgreSQLのインストールからテーブル作成まで解説【初心者向け】
テーブル作成時のデータ型の定義
テーブルを作成する際には、カラムにデータ型を設定します。
設定できるデータ型は、基本的に次のようになります。
| 区分 | データ型 | 詳細 |
|---|---|---|
| 数値 | INTEGER | 整数値（- 2,147,483,648 ～+ 2,147,483,647） |
| NUMERIC(DECIMAL) | 小数点を含む整数 | |
| 文字列 | CHAR | 固定長の文字列 |
| VARCHAR | 可変長の文字列 | |
| 日付 | DATE | 日付 |
| TIMESTAMP | 日付と時間 | |
| 論理値 | BOOLEAN(BIT) | TRUE / FALSE判定 |
データ型はDBMSによって使えるものが異なったり、独自で用意したデータ型なども存在します。
そのため、あなたが使っているDBMSに合わせてリファレンスを確認してみるのが良いです。
厳密にすべてのデータ型を紹介することはできませんが、「INTEGER型」や「CHAR型」はどのDBMSでも頻繁に使われるデータ型なので覚えておきましょう。
データ型については、次の記事でも詳細に解説しているので合わせて確認してみてください！
【関連記事】
▶︎【初心者向け】SQLのデータ型を理解しよう！利用方法も解説！
▶︎【入門編】MySQLで使えるデータ型は？主要な型を徹底解説！
- データ型には大きく分けて「数値」「文字列」「日付」「論理値」の4区分ある
- DBMSによって使えるデータ型は異なる
- 「数値」「文字列」のデータ型は、多くのDBMSで共通して使われるので要チェック！
テーブル作成時の制約の定義
データ型があるから制約はいらないのでは？
多くのDBMSでは、データ型だけの制約だとどうしても精密さに欠けてしまい、扱いにくくなってしまうのです。そのため制約は必要な要素になっています。
テーブルを作成する際には、データ型以外に「制約」の設定も可能です。
制約を設定することによって、テーブル内にあるデータを自由に制御できます。
もし、制約に違反するデータをカラムに格納しようとした場合にエラーが発生し、予期せぬデータが格納されることを防げるのです。
例えば、あるカラムには整数値のみを受け付ける制約をつけたいとします。
しかし、正数のみを格納できる標準のデータ型はないため別で制約が必要です。
また、他のカラムやデータに関連してカラムのデータを制約したい場合もあるでしょう。
こちらも例を挙げると、ある商品の情報を格納しているテーブルには、1つの商品番号に対してデータが2つ以上あってはダメですよね。
このような問題を解決するため、SQLにはカラムやテーブルに対して、制約を定義することができるのです。
SQLの主な制約には、次のようなものがあります。
| 区分 | 制約 | 構文 | 詳細 |
|---|---|---|---|
| 初期値 | – | DEFAULT ‘SQL’ | 初期値に「SQL」を設定 |
| DEFAULT NULL | 初期値に NULL を設定 | ||
| 非NULL制約 | 列制約 | NOT NULL | NULL が入らないように設定 |
| 一意性制約 | 列制約 | UNIQUE | カラムに同じ値が複数入らないように設定 |
| テーブル制約 | UNIQUE(カラム名) | ||
| 主キー制約 | 列制約 | PRIMARY KEY | 主キーを設定（カラムには同じ値が複数入らず、NULLも設定できません） |
| テーブル制約 | PRIMARY KEY（カラム名, …） | ||
| 検査制約 | 列制約 | CHECK（列名 + 条件式） | カッコ内に指定した条件に合致する値しか入らないように設定（カッコ内にカラム名を用いた条件を指定） |
| テーブル制約 | |||
| 参照制約 | 列制約 | REFERENCESテーブル名（カラム名） | FOREIGN KEY（カラム名）で示すカラムに、該当テーブルの該当カラムを参照する外部キーを設定 |
| テーブル制約 | FOREIGN KEY（カラム名） REFERENCES テーブル名（カラム名） |
それぞれの制約について、もう少し詳しく見ていきましょう。
初期値（DEFAULT）
DEFAULTは、テーブルにデータに具体的な値を格納しない場合に、デフォルト値を設定する制約のことです。
例えば、文字列を格納するカラムの初期値を「DEFAULT ‘SQL’」とすることで、何も文字列を指定しなかった場合に「SQL」という文字列が設定されます。
また、「DEFAULT NULL」とすることで初期値に NULL を設定できます。
非NULL制約（NOT NULL）
データベースではカラムを任意で設定できますが、設定時に「NOT NULL」制約を設けることで、カラムにNULL値を指定できないように設定可能です。
カラムにNULL値を絶対に格納したくない場合に使います。
NOT NULLについては、次の記事で詳しく解説していますので参考にしてみてください。
【関連記事】
▶︎【SQL】意外とややこしいnullについての基本的知識とSQLのnot nullを分かりやすく解説。
一意性制約（UNIQUE）
UNIQUE制約を設定すると、設定されたカラムに対しては重複した値の設定が不可能となります。
主キーと似たような働きがありますが、それぞれ設定できる条件が異なります。
詳しくは以下の記事で解説していますので参考までに。
【関連記事】
▶︎【SQL】UNIQUE制約についてのあれこれ。似ている名前”ユニークインデックス”との違いやカラムの組み合わせについて解説
主キー制約（PRIMARY KEY）
PRIMARY KEYとは、値を指定することで「ある1行」を完全に特定できる役割のあるカラムのことです。
日常生活における「マイナンバーカード」や、企業で付与される「社員番号」などが主キーのイメージです。
主キーを設定することによって、テーブルのデータを特定する際に役立つため、よく使われる制約になっています。
PRIMARY KEYについては、以下の記事で解説しているのでチェックしてみてください。
【関連記事】
▶︎【SQL】PRIMARY KEY制約の作成・削除・追加・変更方法まとめ
検査制約（CHECK）
CHECK制約は、ある列に格納される値が妥当であるか、細かく判定したい場合に設定する制約です。
CHECK制約をうまく活用すれば、システムを誤動作させるデータの登録を防げます。
CHECK制約については、次の記事を参考にしてください！
【関連記事】
▶︎SQLのチェック制約の使い方を詳しく解説
参照制約（REFERENCES / FOREIGN KEY）
参照制約は、外部キー制約のことです。
もう少し簡単に説明すると、カラムに任意の値を入れられるのではなく、参照するテーブルの決められたカラムの値（外部キー）を設定します。
図解すると、次の通りです。
■参照元(親)テーブル
| id | name | age |
|---|---|---|
| 1 | potepan | 30 |
| 2 | potepan tarou | 20 |
| 3 | potepan hanako | 10 |
■参照(子)テーブル
| id | user_id | score |
|---|---|---|
| 1 | 2 | 100 |
| 2 | 3 | 89 |
| 3 | 1 | 76 |
参照テーブルの user_id カラムは、参照元テーブルの id カラムの値を参照して設定します。
- データ型の制約を補う形で、テーブル制約・列制約が存在する
- 制約には「初期値」「非NULL制約」「一意性制約」「主キー制約」「検査制約」「参照制約」の6つある
- 主キー制約は、特に重要な制約なので、しっかり覚えておくと良い！
テーブル定義を確認・取得する方法
ここでは、作成したテーブルの定義を確認・取得する方法について確認していきます。
サンプルとして、次のテーブルを用意しました。
| CREATE TABLE employee( employee_num INT(6) PRIMARY KEY, name CHAR(20) NOT NULL, mail CHAR(30) DEFAULT NULL, group_num INT(4) REFERENCES department (department_num) ); |
・社員テーブルを定義 ・社員番号に 主キー制約 を設定 ・社員名に 非NULL制約 を設定 ・メールに 初期値 NULL を設定 ・ グループ番号を、部門テーブルの「部門番号」カラムを参照する外部キーに設定 |
このテーブルの各DBMSごとの確認方法について見ていきましょう。
MySQLの場合
MySQLでテーブル定義を確認するには、次のように記述します。
SHOW FULL COLUMNS FROMテーブル名;
実際にemployee テーブルの定義を確認してみると、次のようにテーブル定義が表示されます。
mysql> SHOW FULL COLUMNS FROM employee; +--------------+----------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------------+----------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | employee_num | int | NULL | NO | PRI | NULL | | select,insert,update,references | | | name | char(20) | utf8mb4_0900_ai_ci | NO | | NULL | | select,insert,update,references | | | mail | char(30) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | | | group_num | int | NULL | YES | | NULL | | select,insert,update,references | | +--------------+----------+--------------------+------+-----+---------+-------+---------------------------------+---------+ 4 rows in set (0.02 sec)
各カラムのデータ型や制約の設定状況がわかるようになっています。
また、「テーブル構造」もしくは「クエリの実行計画に関連した情報」を取得するには、次のように記述します。
DESC テーブル名;
こちらも実際に実行して確認してみます。
mysql> DESC employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | employee_num | int | NO | PRI | NULL | | | name | char(20) | NO | | NULL | | | mail | char(30) | YES | | NULL | | | group_num | int | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
このように、カラムの情報を FULL で取得した場合よりも、情報量を少なく取得できました。
MySQLでテーブル定義の確認をしたい場合は、リファレンスの「SHOW COLUMNS 構文」を確認すると良いでしょう。
PostgreSQLの場合
PostgreSQLでテーブル定義を確認するには、次のように記述します。
¥d テーブル名;
employee テーブルの定義を確認してみると、次のようになるはずです。
mydb=# \d employee;
Table "public.employee"
Column | Type | Collation | Nullable | Default
--------------+---------------+-----------+----------+--------------
employee_num | integer | | not null |
name | character(20) | | not null |
mail | character(30) | | | NULL::bpchar
group_num | integer | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (employee_num)
Foreign-key constraints:
"employee_group_num_fkey" FOREIGN KEY (group_num) REFERENCES department(department_num)
カラム名やデータ型、制約の情報が記載されていますね。
Oracleの場合
Oracleでテーブル定義を確認するには、次のように記述します。
DESCRIBE テーブル名;
また、「DESCRIBE」は次のように省略して記述可能です。
DESC テーブル名;
上記以外に、次の方法でもテーブル定義を確認できます。
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'テーブル名';
執筆者がOracleデータベースを所有していないため、表示結果は省略します。
テーブル定義を変更する方法
テーブルを作成後に定義を変更したいのですが、できますか？
はい、可能です！テーブル定義の変更方法を見ていきましょう！
テーブル定義を変更するには「ALTER TABLE文」を使います。
テーブル定義の変更方法をDBMSごとに確認していきましょう。
MySQLの場合
MySQLでテーブル定義を変更するには、「ALTER TABLE CHANGE文」または「ALTER TABLE MODIFY文」を使います。
使い方はそれぞれ次の通りです。
ALTER TABLE テーブル名 CHANGE 変更前カラム名 変更後カラム名 カラム定義; ALTER TABLE テーブル名 MODIFY カラム名 カラム定義;
ALTER TABLE CHANGE文は、カラム名とカラム定義を変更できます。
一方、ALTER TABLE MODIFY文は、カラム定義のみを変更可能です。
ではサンプルを用いて確認してみます。
employee テーブルの employee_num カラム のデータ型を変更します。
mysql> SHOW COLUMNS FROM employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | employee_num | int | NO | PRI | NULL | | | name | char(20) | NO | | NULL | | | mail | char(30) | YES | | NULL | | | group_num | int | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ 4 rows in set (0.04 sec)
次のように記述しましょう。
ALTER TABLE employee MODIFY employee_num char(6);
テーブル定義を確認してみると、ちゃんとデータ型が変更されているのがわかります。
mysql> SHOW COLUMNS FROM employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | employee_num | char(6) | NO | PRI | NULL | | | name | char(20) | NO | | NULL | | | mail | char(30) | YES | | NULL | | | group_num | int | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
次に、ALTER TABLE CHANGE文でカラム名とカラム定義を変更します。
ALTER TABLE employee CHANGE employee_num id int;
変更後のテーブル定義を確認すると、カラム名とデータ型が変更されているはずです。
mysql> SHOW COLUMNS FROM employee; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | char(20) | NO | | NULL | | | mail | char(30) | YES | | NULL | | | group_num | int | YES | | NULL | | +-----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
PostgreSQLの場合
PostgreSQLでテーブル定義を変更するには、「ALTER TABLE ALTER COLUMN文」を使います。
記述方法は次の通りです。
ALTER TABLE テーブル名 ALTER COLUMN カラム名 TYPE データ型;
実行結果は省略しますが、指定したカラムのデータ型が変更されます。
また、その他の制約を変更したい場合は、次の通りです。
■制約を追加 ALTER TABLE テーブル名 ADD CONSTRAINT カラム名 制約名 (値); ■制約を削除 ALTER TABLE テーブル名 DROP CONSTRAINT カラム名; ■非NULL制約の追加 ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET NOT NULL; ■非NULL制約の削除 ALTER TABLE テーブル名 ALTER COLUMN カラム名 DROP NOT NULL; ■初期値の変更 ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET DEFAULT 初期値; ■初期値の削除 ALTER TABLE テーブル名 ALTER COLUMN カラム名 DROP DEFAULT;
Oracleの場合
Oracleでテーブル定義を変更するには、「ALTER TABLE MODIFY文」を使います。
記述方法は次の通りです。
ALTER TABLE テーブル名MODIFY ( カラム名 カラム定義 ) ;
定義の変更は、カンマ区切りで複数変更することもできます。
ALTER TABLE テーブル名MODIFY (
カラム名1 カラム定義1,
カラム名2 カラム定義2,
カラム名3 カラム定義3
) ;
初期値 と 非NULL制約の変更方法は次の通りです。
■初期値の変更 ALTER TABLE テーブル名MODIFY ( カラム名 DEFAULT '初期値' ) ; ■初期値の削除 ALTER TABLE テーブル名 MODIFY ( カラム名 DEFAULT NULL ) ; -- NULL にすることで事実上の削除としている ■非NULL制約の設定 ALTER TABLE テーブル名 MODIFY ( カラム名 NOT NULL ) ; ■非NULL制約の削除 ALTER TABLE テーブル名 MODIFY ( カラム名NULL ) ; -- NULL にすることで事実上の削除としている
また、データ型と制約は記述を組み合わせて同時に変更もできます。
テーブル定義のコピーは可能？
テーブル定義をコピーしてテーブルを新しく作ることはできますか？
はい、可能です！MySQL と PostgreSQL の場合を見ていきましょう。
MySQLでテーブル定義をコピーするには、次のように記述します。
CREATE TABLE コピー先テーブル名 LIKE コピー元テーブル名;
PostgreSQLでテーブル定義をコピーするには、次のように記述します。
CREATE TABLE コピー先テーブル名( LIKE コピー元テーブル名 オプション);
PostgreSQLはオプションを指定することで、コピーの範囲を変えられます。
| オプション | 詳細 |
|---|---|
| INCLUDING DEFAULTS | DEFAULT制約をコピー |
| INCLUDING IDENTITY | IDENTITY の指定をコピー カラムに割り当てられるシーケンスはコピー元とコピー先で異なる |
| INCLUDING CONSTRAINTS | CHECK制約をコピー |
| INCLUDING INDEXES | PRIMARY KEY、UNIQUE制約、EXCLUDE 制約をコピー |
| INCLUDING COMMENTS | カラム、制約、インデックスに関するコメントをコピー |
| INCLUDING STATISTICS | 拡張統計情報をコピー拡張統計情報をコピー |
| INCLUDING STORAG | カラム定義に関するSTORAGE設定をコピー |
| INCLUDING ALL | すべてのオプションを指定してコピー |
必要に応じてオプションを使い分けましょう！
まとめ
SQLのテーブル定義について解説しました。
テーブル定義は、テーブルを作成時に重要な要素です。
作成後にも変更や削除は可能なので、ぜひこの記事を参考にテーブル定義の設定・変更方法などをマスターしてください！
