以前の記事
サブ言語
DDLとDML
SQLにはサブ言語がいくつか定義されており、この連載では、DDLとDMLを扱います。データ定義言語（DDL）は、データベースのデータ構造を定義する言語です。DDLは、Data Definieiton Languageの略です。DDLは、表の作成（CREATE）、列の更新（ALTER）などの命令を行います。データ操作言語（DML）は、Data Manipulation Languageのことで、データの検索（SELECT）、挿入（INSERT）などの命令が含まれます。
CREATE
CREATE文は、テーブルの構造（スキーマ）を定義する時に利用します。これからステップバイステップで、学生クラブのデータベースを作成していきます。このデータベースには、学生、クラブ、クラブメンバーという3つのテーブルが含まれています。
CREATEの構文はシンプルで、基本的にはテーブル名と、属性名と属性のデータ型のペアのリストを書くだけになります。
CREATE TABLE student ( sid INTEGER, name CHAR(20), age INTEGER, major CHAR(20));
PK
この命令でデータベースにテーブルを作ることができますが、このテーブルを有益なものにするために、主キー（Primary Key）を設定します。主キーとは、テーブル内の1行を一意に識別するための検索キーです。このキーをある属性に対して設定すると、その列（主キー列）では、値の重複が禁止されます。この制約を、主キー制約と呼びます。定義の仕方は、PRIMARY KEY（対象の属性名）をリストに加えるだけです。
CREATE TABLE student ( sid INTEGER, name CHAR(20), age INTEGER, major CHAR(20), PRIMARY KEY (sid));
この例だと、主キー制約によって、同じ学生番号（sid）を持つ学生を登録することができなくなります。主キーは、複数の列から作ることもできます（複合主キー）。例えば、PRIMARY KEY（name, age）とすると、氏名と年齢が同じ学生を重複して登録することができなくなります。
他の2つのテーブルも主キー付きで作成してみます。
CREATE TABLE club ( cid INTEGER, name CHAR(20), budget INTEGER, PRIMARY KEY (cid)); CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid));
club_memberの主キーを複合主キーにした理由は、1人の学生があるクラブに複数登録されないようにするためです。
FK
もう1つ、加えたい制約があります。外部キーによる制約です。外部キー（Foreign Key） とは、特定のテーブルの列を参照するためのキー（ポインター）です。このポインターを利用すると、2つのテーブルの間に関連（親子関連）をもたらすことができます。
それでは、club_memberとstudentテーブルの間に親子関連を持たせてみます。
CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES student(sid));
CREATE文に、FOREIGN KEY（自身のPK）REFERENCES 参照先のテーブル名（参照先PK）が追加されたのがわかると思います。これで、参照元（club_member）が子テーブル、参照先（student）が親テーブルとなります。
外部キーは、主キーと違い1つのテーブルに対して複数設定できます。club_memberとclubの間にも親子関連を持たせます。
CREATE TABLE club_member ( sid INTEGER, cid INTEGER, joined_at DATE, PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES student(sid), FOREIGN KEY (cid) REFERENCES club(cid));
さて、外部キーを設定すると、具体的に何が嬉しいのでしょうか。主キー同様、外部キーによって課される制約を外部キー制約（または、参照整合性制約）と呼びます。この制約によって以下2つのことが禁止されます。
- 子テーブルの外部キーの値は親テーブルの主キーに存在する値でないといけない
- 親テーブルが持つ参照されているキーに対してのUPDATE/DELETEを禁止
もう少し説明すると、1に関しては、例えば、学生番号が4の学生をclub_memberテーブルに追加することができなくなります。なぜなら、studentテーブルにsidが4の学生が存在しないからです。2に関しては、例えば学生番号1の学生を削除することができなくなります。理由は、club_memberにsidが1のレコードが存在するからです。何となく外部キー制約のイメージが掴めたでしょうか。
MySQLの参照アクション
禁止事項の2つ目は、MySQLでは参照アクションと呼ばれています。参照アクションは、親テーブルが持つ参照されているキーに対してUPDATEやDELETEをした場合の子テーブルの外部キーに与える影響を指します。UPDATEとDELETEそれぞれに対して、4つのアクションから1つ選ぶことができます。先ほど説明した制約は、MySQLではデフォルトの挙動になっています。
シンタックスは以下のような形になります。
FOREIGN KEY (sid)
REFERENCES student
ON UPDATE アクション名
ON DELETE アクション名
PKとFKを設定しよう
基本的に主キーと外部キーは設定しておきましょう。主キーは、行の重複を防いだり、クエリで個別の行を参照可能にしたり、外部キー参照をサポートしたりなどのメリットがあります。外部キーを用いると、参照整合性が保たれ、CASCADEアクションを実現できます。このような手間なことはアプリ側でやらずにデータベースに任せましょう。
ここまでの要点
RDBMSはリレーショナルモデルがベースでした。このデータベースの特徴を構造、操作、整合性の3つの観点に着目すると以下のようにまとめることができます。
- 構造：テーブル
- 操作：SQL
- 整合性：主キー制約、外部キー制約
データベースを比較する際は、この3つの軸を用いると比較が容易になると思います。さらに、SQLにはサブ言語が存在することと、その中のデータ定義言語に含まれるCREATE TABLE文の基礎を学びました。次はデータ操作言語のSELECTをみていきます。
.png)
