Mysqlのメモ
概要
学生の課題みたいだけど、学校の成績管理表を作成してみる。
外部KEYの使い方についてのメモです。
tableの定義
# 生徒管理テーブル(student)
# 科目管理テーブル(subject)
# 成績管理テーブル(score)
やりたい事
# 1つのテーブルでデータの重複をさせない制約をつける。
# 生徒管理テーブルからデータがUPDATE/DELETEされたら、成績管理テーブルからもデータをUPDATE/DELETE。
# 科目管理テーブルからデータがUPDATE/DELETEされたら、成績管理テーブルからもデータをUPDATE/DELETE。
データの重複をさせない(UNIQUEな値を入れる)
カラム定義の最後にUNIQUEを指定します。そうすると一意な値のみ登録が可能になります。例えば科目名とか。
CREATE TABLE IF NOT EXISTS `Subject` ( `id` INT NOT NULL , `subject` VARCHAR(45) NULL UNIQUE ,
外部KEY制約を利用する。
Engine TypeをInnoDBに設定する。必ず外部KEY制約を設けるtableの両方をInnoDBに設定する。
両方InnoDBに設定しないとtableをcreateする際にエラーが出る。Can't create table './mysql/table.frm' (errno: 150)
FOREIGN KEY(a) REFERENCE parent(b)を利用する。
※error:150の原因は他にもあるようです。Mysqlのリファレンスに書いてあります。
http://dev.mysql.com/doc/refman/4.1/ja/innodb-foreign-key-constraints.html
SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `score` ( `id` INT NOT NULL AUTO_INCREMENT , `studentid` INT NULL , `subjectid` INT NULL , `score` INT NULL , PRIMARY KEY (`id`), FOREIGN KEY( `studentid` ) REFERENCE student( `id` ) ON UPDATE CASCADE ON DELETE CASCADE FOREIGN KEY( `subjectid` ) REFERENCE subject( `id` ) ON UPDATE CASCADE ON DELETE CASCADE
外部KEY制約のオプション
ON UPDATE CASCADE ON DELETE CASCADE
のようにカラムを定義すると親テーブルのUPDATEやDELETEを受けて子テーブルにそのActionを反映できる。
* | ON DELETE | ON UPDATE |
---|---|---|
CASCADE | 親テーブルと同じ値を子テーブルからも削除 | 親テーブルと同じ値を持つ子テーブルの値を更新 |
RESTRICT | 制約エラー | 制約エラー |
SET NULL | 子テーブルにNULLを設定 | 子テーブルにNULLを設定 |
NO ACTION | デフォルトのエラー | デフォルトのエラー |
※ NOACTION とRESTRICT の違い
NOACTION
削除もしくは更新が外部キー制約違反となることを示すエラーを発生します。制約が遅延可能な場合、何らかの参照行が存在する限り、このエラーは制約の検査時点で発生します。これはデフォルトの動作です。
RESTRICT
削除もしくは更新が外部キー制約違反となることを示すエラーを発生します。検査が遅延できない点を除き、NO ACTIONと同じです。
SQL全文
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; DROP SCHEMA IF EXISTS `mydb` ; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; SHOW WARNINGS; USE `mydb` ; -- ----------------------------------------------------- -- Table `Student` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Student` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Student` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL , `mailaddress` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `Subject` -- ----------------------------------------------------- DROP TABLE IF EXISTS `Subject` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `Subject` ( `id` INT NOT NULL , `subject` VARCHAR(45) NULL UNIQUE , PRIMARY KEY (`id`) ) ENGINE = InnoDB; SHOW WARNINGS; CREATE UNIQUE INDEX `subject_UNIQUE` ON `Subject` (`subject` ASC) ; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `score` -- ----------------------------------------------------- DROP TABLE IF EXISTS `score` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `score` ( `id` INT NOT NULL AUTO_INCREMENT , `studentid` INT NULL , `subjectid` INT NULL , `score` INT NULL , PRIMARY KEY (`id`), FOREIGN KEY( `studentid` ) REFERENCE student( `id` ) ON UPDATE CASCADE ON DELETE CASCADE FOREIGN KEY( `subjectid` ) REFERENCE subject( `id` ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = InnoDB; SHOW WARNINGS; -- ----------------------------------------------------- -- Placeholder table for view `view1` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `view1` (`id` INT); SHOW WARNINGS; -- ----------------------------------------------------- -- View `view1` -- ----------------------------------------------------- DROP VIEW IF EXISTS `view1` ; SHOW WARNINGS; DROP TABLE IF EXISTS `view1`; SHOW WARNINGS; USE `mydb`; ; SHOW WARNINGS; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;