超電磁砲でデータベース（テーブル作成編２）

はじめに

今回は外部キーがテーマなので、まずは外部キーを有効化する。また、前回定義したテーブルを利用するが、念のため一旦破棄して再び定義する。もし存在しなければエラーとなるが、それはそれで問題はない。次の命令をコピペして実行して欲しい。

PRAGMA foreign_keys = ON;
DROP TABLE A_TEST ;
DROP TABLE S_TEST ;

CREATE TABLE S_TEST(
 NAME TEXT PRIMARY KEY,
 LEVEL INTEGER NOT NULL DEFAULT 0,
 SKILL TEXT,
 CHECK( 0 <= LEVEL ),
 CHECK( LEVEL <= 5 ) 
) ;

CREATE TABLE A_TEST(
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES S_TEST( NAME )
) ;

外部キー

外部キーの動作を実際 SQL を実行して確認していこう。次の命令で テーブル S_TEST にデータを登録する。

INSERT INTO S_TEST VALUES ( '上条当麻', 0, 'イマジンブレイカー' ) ;
INSERT INTO S_TEST VALUES ( '初春飾利', 1, 'サーマルハンド' ) ;

テーブル A_TEST の属性 NAME はテーブル S_TEST の属性 NAME を参照している。そのため A_TEST.NAME に対応する S_TEST.NAME が存在しなければならない。

解説
(1) １つ目の命令は問題なく成功する。２つ目の命令はテーブル S_TEST に対応するデータが存在しないためエラーになる。
(2) テーブル S_TEST に対応するデータを追加したので成功する。

続けて、次の命令がエラーになることを確認しよう。
DROP TABLE S_TEST ;
DELETE FROM S_TEST WHERE NAME = ‘神裂火織’ ;

これらの削除命令がエラーになるのは外部キーの参照制約が満たされなくなってしまうからだ。実際、DELETE FROM S_TEST WHERE NAME = ‘上条当麻’ ; はテーブル A_TEST に参照しているデータが存在しないため成功する。参照制約が損なわれない仕組みがあれば削除可能になる。ここでは、参照しているデータも連動して削除する方法を解説しよう。そのため、A_TEST を削除して作成し直す。データも再登録しておく。

DROP TABLE A_TEST ;

CREATE TABLE A_TEST(
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES S_TEST( NAME ) ON DELETE CASCADE
) ;

INSERT INTO A_TEST VALUES ( '初春飾利', '柵川中' ) ;
INSERT INTO A_TEST VALUES ( '神裂火織', '天草式十字凄教' ) ;

ON DELETE CASCADE を追加した。これは親テーブル（参照先）のデータを削除した時に連動して削除することを表している。ON UPDATE CASCADE もあり、これは連動して属性値を変更する。他にはON DELETE SET NULL や ON DELETE SET DEFAULT が使えることもある。

解説
　DELETE 時の動作は設定してあるので１つ目の命令は成功し、 テーブル S_TEST, A_TEST の両方から「神裂火織」のデータは削除される。UPDATE 時の動作は設定されていないので、２つ目の命令は参照制約違反でエラーとなる。

ついでに ON UPDATE CASCADE の動作も確認しておこう。再度、次の命令でテーブルを作成し直す。

DROP TABLE A_TEST ;

CREATE TABLE A_TEST(
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES S_TEST( NAME ) ON UPDATE CASCADE
) ;

INSERT INTO A_TEST VALUES ( '初春飾利', '柵川中' ) ;

解説
　今度は２つのテーブルで「初春飾利」が「御坂美琴」に書き換わっている。

テーブル設計

テーブルの設計について簡単に補足しておく。設計については、関数従属性、正規形、ER図などが関係してくるので、ここで詳しく解説するつもりはない。設計に関心がある方は情報処理技術者試験データベーススペシャリストの勉強をされると良いだろう。

関数従属性を知らない方にはさっぱり分からないと思うが、属性 NAME, LEVEL, SKILL, TEAM の間には、関数従属 NAME \(\longrightarrow\) { LEVEL, SKILL } がある。これは NAME が決まると LEVEL, SKILL が一意に決まることを含んでいて、各人物に１つしか SKILL がないという前提となっている。この前提が崩れると設計を見直す必要がある。本来は ER図で書くべきだろうが１行で簡単に記載すると、次の多対多の関係が得られる。ここで、主キーを太字で表した。

( NAME, LEVEL, SKILL ) \( \longleftrightarrow \) ( TEAM )

多対多の関係は次のように１対多の関係に書き換える。中央のテーブルは中間テーブルと呼ばれ、外部キーからなる。

( NAME, LEVEL, SKILL ) \( \longrightarrow \) ( NAME, TEAM ) \( \longleftarrow \)( TEAM )

このような理由で本来は右端のテーブルが存在するべきであるが、SQLの演習としては煩雑になるだけなので省略した。