Mysqlのメモ - Y's note

概要

学生の課題みたいだけど、学校の成績管理表を作成してみる。
外部KEYの使い方についてのメモです。

tableの定義

# 生徒管理テーブル(student)
# 科目管理テーブル(subject)
# 成績管理テーブル(score)

ER図

やりたい事

# １つのテーブルでデータの重複をさせない制約をつける。
# 生徒管理テーブルからデータが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;