SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

直近開催のイベントはこちら!

Data Tech 2022

2022年12月8日(木)10:00~15:50

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

MySQL事始

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

第11回

外部キーのサポート

 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以外の各ストレージエンジンの特徴をご紹介いたします。

この記事は参考になりましたか?

  • Facebook
  • Twitter
  • Pocket
  • note
MySQL事始連載記事一覧

もっと読む

この記事の著者

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

日本オラクル MySQL Global Business Unit Asia Pacific & Japan担当 MySQLソリューション・エンジニアリング・ディレクターオラクル社において、MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なM...

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/3774 2012/02/22 00:00

Job Board

PR

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

2022年12月8日(木)10:00~15:50

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング