外部キーのサポート
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 SETやCOLLATEも含めて同じになっていることが求められます。ここで、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 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以外の各ストレージエンジンの特徴をご紹介いたします。
