EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

第11回 MySQL事始 ストレージエンジン(2) InnoDBストレージエンジン

edited by DB Online   2012/02/22 00:00

外部キーのサポート

 MySQL 5.5およびMySQL Cluster 7.1ではInnoDBのみが外部キーをサポートしています。外部キーは外部キー制約とも呼ばれ、ある列の値が外部のテーブルの列に格納されていることを表します。外部キーは、ある値が必ず他のテーブルに存在するというデータベースの関係整合性を実現するために使用されます。例えばCityテーブルに格納されているCountryCodeが、必ずCountryテーブルにも存在するように設定するには、CityテーブルのCountryCode列に、CountryテーブルのCode列を参照する外部キーを設定します。参照される側の列には主キーかユニークキーを通常は設定します。

 例:CountryCode列に外部キーを設定したCityテーブル
CREATE TABLE City (
	ID INT NOT NULL AUTO_INCREMENT,
	Name CHAR(35) NOT NULL DEFAULT '',
	CountryCode CHAR(3) NOT NULL DEFAULT '',
	District CHAR(20) NOT NULL DEFAULT '',
 	Population INT NOT NULL DEFAULT 0,
	PRIMARY KEY (ID),
	KEY CountryCode (CountryCode),
	FOREIGN KEY (CountryCode) REFERENCES Country(Code)
)

 外部キーを設定する場合、参照元と参照先の列は同じデータ型である必要があります。整数型の場合にはUNSIGNEDの有無、文字列型の場合にはCHARACTER SETCOLLATEも含めて同じになっていることが求められます。ここで、CityテーブルのID列を参照する外部キーを持ったテーブルを作成してみます。

mysql> CREATE TABLE fktest (
    -> CityId INT,
    -> name CHAR(8),
    -> FOREIGN KEY (CityId) REFERENCES City(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

 参照される側のCityテーブルのID列の最大値を確認します。

mysql> SELECT MAX(id) FROM City;
+---------+
| MAX(id) |
+---------+
|    4079 |
+---------+
1 row in set (0.02 sec)

 CityテーブルのID列に存在しない値をテスト用のテーブルに格納しようとすると、参照整合性に問題があるためエラーとなります。

mysql> INSERT INTO fktest VALUES (4080, 'test');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`test`.`fktest`
CONSTRAINT `fktest_ibfk_1` FOREIGN KEY (`CityId`) REFERENCES `City` (`ID`))

 データが存在していれば問題なく値を格納できます。

mysql> INSERT INTO City(ID, Name, CountryCode) VALUES(4080, 'testcity','JPN');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO fktest VALUES(4080, 'test');
Query OK, 1 row affected (0.00 sec)

 またCityテーブルのIDが4080の列を削除しようとすると、このデータを参照している値が存在しているため削除できません。

mysql> DELETE FROM City WHERE ID = 4080;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`test`.`fktest`, CONSTRAINT `fktest_ibfk_1` FOREIGN KEY
(`CityId`) REFERENCES `City` (`ID`))

 外部キーのオプションでON UPDATEON DELETEを設定すると、参照される側のデータの更新や削除時に参照元のデータを連動させて変更させることができます。

ON UPDATEやON DELETEのオプション
CASCADE 参照される側の操作と同様に参照元も変更する
NO ACTION 参照しているデータが存在すれば処理がエラーとなる
RESTRICT NO ACTIONと同じ
SET NULL 参照しているデータをNULLにする

  なお、InnoDB以外のストレージエンジンは外部キーをサポートしていないため、テーブルの作成やALTER文でのエンジン変更時にはエラーとなります。

mysql> ALTER TABLE fktest ENGINE = MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key
constraint fails

 次回はInnoDB以外の各ストレージエンジンの特徴をご紹介いたします。



著者プロフィール

  • 梶山隆輔(カジヤマリュウスケ)

    日本オラクル株式会社 MySQL Global Business Unit Asia Pacific & Japan担当 MySQL Principal Sales Consultant オラクル社において、MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なMySQL部門にて、台湾、オーストラリア、インドに在籍するチームメンバーを束ね、アジア太平洋地域の25以上の国や地域でのMySQL普及やビジネスの拡大をミッションとする。  

バックナンバー

連載:MySQL事始

もっと読む

All contents copyright © 2007-2020 Shoeisha Co., Ltd. All rights reserved. ver.1.5