制約を定義する

CREATE TABLE構文は既に学びましたが、正規形で分割されたテーブルを使うための『外部キー制約（参照制約）』他、いくつか新たな構文について解説します。

外部キー制約（参照制約）

ある列が外部キーであることを定義するには、CREATE TABLEの列定義部分で

FOREIGN KEY（外部キーにする列名） REFERENCES 参照先テーブル名（参照先列名）
[ 参照先レコードを更新・削除しようとした場合の動作 ]

と記述します。
このとき、参照先テーブルおよび参照先列は先に存在していなければなりません。

オプションで、参照先レコードを更新・削除しようとした場合の動作を指定することも出来ます。

更新された場合は

ON UPDATE [動作]

削除された場合の動作は

ON DELETE [動作]

と記述します。両方を指定することもできます。
動作として指定できるのは次の通りです。

RESTRICT	エラーが発生する（デフォルト動作）
CASCADE	参照元のレコードも削除または更新する
SET NULL	参照元のレコードの外部キーカラムにNULLを代入する
NO ACTION	MySQLの場合はRESTRICTと同じ

省略された場合は、RESTRICT が指定されたものと見なされます。

たとえば、『参照先のレコードが削除されたら、そのレコードを参照していたレコードも削除する』場合は

SQL

ON DELETE CASCADE

です。

演習

それでは実際に外部キー制約のあるテーブルを作ってみましょう。
サンプルとして、前節の最後で設計した以下のテーブルを作成してみます。

【学生】

テーブル名：student_list

項目	列名	型	制約
学籍番号	student_id	char(4)	PRIMARY KEY
学生名	student_name	varchar(15)

【講師】

テーブル名：teacher_list

項目	列名	型	制約
講師ID	teacher_id	char(3)	PRIMARY KEY
講師名	teacher_name	varchar(15)
内線番号	ex_number	char(4)

【科目】

テーブル名：subject_list

項目	列名	型	制約
科目ID	subject_id	char(3)	PRIMARY KEY
科目名	subject_name	varchar(20)
担当講師ID	teacher_id	char(3)	FOREIGN KEY teacher_list(teacher_id)

【科目履修状況】

テーブル名：registration_list

項目	列名	型	制約
学籍番号	student_id	char(4)	PRIMARY KEY FOREIGN KEY student_list(student_id)
科目ID	subject_id	char(3)	PRIMARY KEY FOREIGN KEY subject_list(subject_id)

これらのテーブルを作成するSQLを記述してみましょう。

【student_list】には外部キーはないので、以前学んだ通りです。

SQL

CREATE TABLE student_list (
	student_id	char(4),
	student_name	varchar(15),

	PRIMARY KEY(student_id)
);

【teacher_list】にも外部キーはないので、以前学んだ通りです。

SQL

CREATE TABLE teacher_list (
	teacher_id	char(3),
	teacher_name	varchar(15),
	ex_number	char(4),

	PRIMARY KEY(teacher_id)
);

【subject_list】テーブルのteacher_idは【teacher_list】テーブルのteacher_idを参照する外部キーなので、以下のようになります。参照先の更新・削除にはエラーが発生するように設定します。

SQL

CREATE TABLE subject_list (
	subject_id	char(3),
	subject_name	varchar(20),
	teacher_id	char(3),
	PRIMARY KEY(subject_id),
	FOREIGN KEY(teacher_id) REFERENCES teacher_list(teacher_id)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);

今回は文例のため敢えて記述しましたが、『更新・削除時にエラーを発生させる』のはデフォルト動作なので、ON UPDATE RESTRICTおよびON DELETE RESTRICTを記述せず、

SQL

CREATE TABLE subject_list (
	subject_id	char(3),
	subject_name	varchar(20),
	teacher_id	char(3),
	PRIMARY KEY(subject_id),
	FOREIGN KEY(teacher_id) REFERENCES teacher_list(teacher_id)
);

としても結果は同じです。

【registration_list】のstudent_idは【student_list】のstudent_idを、
【registration_list】のsubject_idは【subject_list】のsubject_idを参照する外部キーなので、以下のようになります。

SQL

CREATE TABLE registration_list (
	student_id	char(4),
	subject_id	char(3),

	PRIMARY KEY(student_id, subject_id),
	FOREIGN KEY(student_id) REFERENCES student_list(student_id)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT,
	FOREIGN KEY(subject_id) REFERENCES subject_list(subject_id)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);