MySQL外部キー:データベース初心者入門のMySQLで外部キーを定義する方法

ソース:http://tech.ddvip.com/2007-05/118009486725743.html
 
データテーブルの定義
コンピューターメーカーの場合、そのデータベースには機械全体と部品の製品情報が保存されています.機械全体の製品情報を保存するためのテーブルをPcと言います.部品供給情報を保存するためのテーブルをPartsといいます.
PCテーブルには、このコンピュータで使用されているCPUのモデルを記述するフィールドがあります.
Partsテーブルには、CPUのモデルが記述されているフィールドがあり、すべてのCPUのモデルリストと考えることができます.
明らかに、このメーカーが生産しているパソコンは、CPUが供給情報テーブル(parts)に存在するモデルに違いない.この場合、2つのテーブルには、PcテーブルのCPUモデルがPartsテーブルのモデルによって制約される制約関係(constraint)が存在する.
まずpartsテーブルを作成します.CREATE TABLE parts (
... ...,
model VARCHAR(20) NOT NULL,
... ...
);
 
次はPcテーブルです.CREATE TABLE pc (
... ...,
cpumodel VARCHAR(20) NOT NULL,
... ...
};
 
索引の設定
外部キーを設定するには、参照テーブル(reference table、すなわちPcテーブル)と被参照テーブル(referenced table、すなわちpartsテーブル)の両方に対応するフィールドにインデックス(index)を設定する必要があります.
Partsテーブル:
　　ALTER TABLE parts ADD INDEX idx_model (model);
partsテーブルにインデックスを追加し、インデックスをmodelフィールドに作成し、このインデックスにidx_という名前を付けます.model.
Pcテーブルについても似ています.
　　ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);
実際には、この2つのインデックスは、テーブルを作成するときに設定できます.ここはただその必要性を際立たせるためだ.
外部キーの定義
次に、2つのテーブルの間に前述したような「制約」を確立します.PCのCPUモデルはpartsテーブルの対応するモデルを参照する必要があるため、Pcテーブルのcpumodelフィールドを「外部キー」(FOREIGN KEY)に設定します.つまり、このキーの参照値は他のテーブルから来ます.ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model);
 
最初の行はPcテーブルに外部キーを設定し、この外部キーにfk_という名前を付けるということです.cpu_model;2行目は、このテーブルのcpumodelフィールドを外部キーに設定することです.3行目は、この外部キーが受ける制約がPartsテーブルのmodelフィールドから来ていることを示します.
これで、私たちの外部キーでいいです.CREATE 1台のPcを試してみると、使用しているCPUのモデルがPartsテーブルに存在しないので、MySQLはこのPCがCREATEによって出てくることを禁止します.
カスケード操作
次のような状況を考慮します.
技術者は、1ヶ月前にpartsテーブルに入力されたシリーズのcpu(多くのモデルがある可能性がある)のモデルがすべて1文字間違っていることを発見し、今修正する必要があります.partsテーブルのReferenced Columnが変化すると、対応するテーブルのReferenceg Columnも自動的に修正されることを望んでいます.
外部キーを定義するときに、最後にこのようなキーワードを追加できます.
　　ON UPDATE CASCADE; つまり、メインテーブルが更新されると、サブテーブルに連鎖更新動作が発生し、これを「カスケード」操作と呼ぶ人もいるようです.
この文を完全に書けば、次のようになります.ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model)
ON UPDATE CASCADE;
 
CASCADEのほか、RESTRICT(マスターテーブル変更禁止)、SET NULL(サブテーブル対応フィールドが空に設定されている)などの操作があります.