前回の記事で投稿した いろんなSQL Vol.2 のつづきの記事となります。
環境
Windows 10
MySQL : version(5.7.28)
使用アプリ
コマンドプロンプト(Windowsマーク押して「cmd」って打ったら出てくるやつ)
#16 外部キー制約の設定
外部キー制約を定義するための基本的な構文がこちら↓↓
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
今回は会社の部署と社員を管理するデータを作って試してみます。
まずは親テーブルを作成（データ挿入済み）
-- 親テーブル
mysql> select * from divtb1;
+------------+
| division |
+------------+
| Accounting |
| Marketing |
| Research |
| sales |
+------------+
子テーブル（参照するテーブル）を作成するときに親テーブル（参照されるテーブル）のdivision カラムに対してFOREIGN KEY制約を設定しています。
書式はこちら↓
CREATE TABLE staff(
id INT,
name VARCHAR(10),
division VARCHAR(10),
INDEX div_index(division),
FOREIGN KEY fk_division(division)
REFERENCES divtb1(dvision)
);
問題なくデータが入るか確認
INSERT INTO staff VALUES
(1, 'Yamada', 'sales');
(2, 'Suzuki', 'Marketing');
(3, 'Okada', 'sales');
(4, 'Tanaka', 'Research');
-- 子テーブル
mysql> select * from staff;
+------+--------+-----------+
| id | name | division |
+------+--------+-----------+
| 1 | Yamada | Sales |
| 2 | Suzuki | Marketing |
| 3 | Okada | Sales |
| 4 | Tanaka | Research |
+------+--------+-----------+
ちゃんと入っています。
次に親テーブルの参照先カラムにない値を含むデータを追加して、ちゃんとエラーが出るか確認してみます。
INSERT INTO staff VALUES (5, 'Hatakeyama', 'Manager');
上記のクエリを実行すると
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop03`.`staff`, CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`division`) REFERENCES `divtb1` (`division`))
FOREIGN KEY設定がうまくできていることがわかります。
注意点
- 親テーブル及び子テーブルでは制約の対象となるカラムに対してINDEXが必要
- 親テーブルと子テーブルは同じストレージエンジンを使用する必要がある
- MySQLでFOREIGN KEYを使用できるストレージエンジンはInnoDBとNDB
- 子テーブルの対象カラムと親テーブルの対象カラムは同じデータ型である必要がある。非バイナリ型の場合は文字セットと照合順序は同じでないといけない
- MySQLは、テーブル内の1つの列と別の列の間の外部キー参照をサポートします。（列にそれ自体への外部キー参照を含めることはできません。）これらの場合、 「子テーブルレコード」は同じテーブル内の依存レコードを参照します。
- MySQLでは、外部キーのチェックを高速化し、テーブルスキャンを必要としないように、外部キーと参照キーのインデックスが必要です。参照テーブルには、外部キー列が同じ順序で最初の列としてリストされているインデックスが必要 です。このようなインデックスは、参照テーブルが存在しない場合は自動的に作成されます。外部キー制約を適用するために使用できる別のインデックスを作成すると、このインデックスは後でサイレントに削除される可能性があります。 index_nameが指定されている場合は、前述のように使用されます。
（一部、公式リファレンスより抜粋）
外部キーに関する情報を取得する方法
INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルを参照することで外部キーに関する情報を取得することができる
実行方法はこちら↓（最後の;をメタコマンドの\Gにすると、結果を縦に表示してくれて見やすくなる）
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema='db_name'\G
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: shop03
CONSTRAINT_NAME: PRIMARY
TABLE_CATALOG: def
TABLE_SCHEMA: shop03
TABLE_NAME: divtb1
COLUMN_NAME: division
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULL
*************************** 4. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: shop03
CONSTRAINT_NAME: staff_ibfk_1
TABLE_CATALOG: def
TABLE_SCHEMA: shop03
TABLE_NAME: staff
COLUMN_NAME: division
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: shop03
REFERENCED_TABLE_NAME: divtb1
REFERENCED_COLUMN_NAME: division
関係性
親テーブル : divtb1
子テーブル : staff (こちらのテーブルでFOREIGN KEY制約の設定をしています)
目的 : 外部キーの制約によって変なデータが追加されなくなるので、データの整合性が保たれる。
##16-2 外部キーをあとから追加する方法と削除
追加
ALTER TABLE 子テーブル名 ADD [ CONSTRAINT 制約名 ]
FOREIGN KEY(子の列名1[, 子の列名2...])
REFERENCES 親テーブル名(親の列名1[, 親の列名2...])
子テーブル名 ⇒ 参照する側
親テーブル名 ⇒ 参照される側
制約名 ⇒ CONSTRAINT_NAME となる外部制約キー名を付ける
削除
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
##補足
外部キー制約設定時のエラーは、下記の文で詳細なエラー内容が確認できます。
SHOW ENGINE INNODB STATUS\G
17 紐づいたレコードの更新と削除
ON DELETE CASCADE ⇒ 削除
ON UPDATE CASCADE ⇒ 更新
Twitterのような投稿サイトをイメージしたデータを以下のように作成しました。
テーブル作成時に子テーブルのFOREIGN KEY以下に削除と更新のクエリ文の記述が必要になる。
-- 親テーブル
mysql> CREATE TABLE 投稿一覧 (
-> id INT NOT NULL AUTO_INCREMENT,
-> メッセージ VARCHAR(140),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 子テーブル
mysql> CREATE TABLE コメント一覧(
-> id INT NOT NULL AUTO_INCREMENT,
-> 投稿一覧_id INT,
-> コメント VARCHAR(140),
-> PRIMARY KEY(id)
-> FOREIGN KEY (投稿一覧_id) REFERENCES 投稿一覧(id)
-> ON DELETE CASCADE -- FOREIGN KEY制約で紐付いたレコードの削除
-> ON UPDATE CASCADE -- FOREIGN KEY制約で紐付いたレコードの更新
-> );
Query OK, 0 rows affected (0.03 sec)
#18 LAST_INSERT_ID()
直前に挿入されたレコードのIDを調べてくれる命令文
現在のテーブル状況を確認
mysql> SELECT * FROM 投稿一覧;
+----+---------------------------------------+
| id | メッセージ |
+----+---------------------------------------+
| 1 | はじめまして |
| 2 | 春になったら行きたいところ |
+----+---------------------------------------+
mysql> SELECT * FROM コメント一覧;
+----+-----------------+-------------------------------+
| id | 投稿一覧_id | コメント |
+----+-----------------+-------------------------------+
| 1 | 1 | よろしくおねがいします。 |
| 2 | 1 | はじめまして!! |
+----+-----------------+-------------------------------+
-- 新しい投稿を追加
mysql> INSERT INTO 投稿一覧 (メッセージ) VALUES
-> ("ドライブスポット教えて");
Query OK, 1 row affected (0.01 sec)
-- LAST_INSERT_ID() を使ってidを自動挿入
mysql> INSERT INTO コメント一覧 (投稿一覧_id, コメント) VALUES
-> (LAST_INSERT_ID(), "箱根スカイラインがおすすめ！");
Query OK, 1 row affected (0.00 sec)
-- 確認
mysql> select * from 投稿一覧;
+----+----------------------------------------+
| id | メッセージ |
+----+----------------------------------------+
| 1 | はじめまして |
| 2 | 春になったら行きたいところ |
| 4 | ドライブスポット教えて |
+----+----------------------------------------+
mysql> SELECT * FROM コメント一覧;
+----+-----------------+--------------------------------+
| id | 投稿一覧_id | コメント |
+----+-----------------+--------------------------------+
| 1 | 1 | よろしくおねがいします。 |
| 2 | 1 | はじめまして!! |
| 4 | 4 | 箱根スカイラインがおすすめ！ |
+----+-----------------+--------------------------------+
※LAST_INSERT_ID()をする前に投稿一覧にid3の投稿を作って、削除していた経緯がありidが4になっています。わかりづらくなってしまい、申し訳ありません。
#19 コメントにコメントをつける
現在のテーブルを確認
mysql> SELECT * FROM 投稿一覧;
+----+---------------------------------------+
| id | メッセージ |
+----+---------------------------------------+
| 1 | はじめまして |
| 2 | 春になったら行きたいところ |
| 3 | ドライブスポット教えて |
+----+---------------------------------------+
mysql> SELECT * FROM コメント一覧;
+----+-----------------+--------------------------------+
| id | 投稿一覧_id | コメント |
+----+-----------------+--------------------------------+
| 1 | 1 | よろしくおねがいします。 |
| 2 | 1 | はじめまして!! |
| 3 | 3 | 箱根スカイラインがおすすめ！ |
+----+-----------------+--------------------------------+
子テーブル作成時に親テーブルの投稿についたコメントと紐付けるカラムを追加
mysql> CREATE TABLE コメント一覧 (
-> id INT NOT NULL AUTO_INCREMENT,
-> 投稿一覧_id INT,
-> コメント VARCHAR(140),
-> 投稿コメント_id INT, -- 親テーブルの投稿についたコメントと紐付けるカラムを作成
-> PRIMARY KEY (id),
-> FOREIGN KEY (投稿一覧_id) REFERENCES 投稿一覧(id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> );
Query OK, 0 rows affected (0.01 sec)
レコード挿入時に投稿コメント_idを追加（コメントから見て親となるコメントがないものはNULLを、親となるコメントがあるものはINSERT文の上から順番に数えていった数を値として書く）
mysql> INSERT INTO コメント一覧 (投稿一覧_id, コメント, 投稿コメント_id) VALUES
-> (1, 'よろしくおねがいします', NULL),
-> (1, 'はじめまして!!', NULL),
-> (3, '箱根スカイラインがおすすめ！', NULL),
-> (3, '途中、箱根芦ノ湖展望公園に寄ってみて', 3),
-> (1, 'はじめまして！', 2),
-> (3, 'あそこいいよね！富士山が見える', 4);
Query OK, 6 rows affected (0.00 sec)
#20 コメントのコメントを抽出する
現在のテーブルを確認
mysql> SELECT * FROM 投稿一覧;
+----+-----------------------------------------+
| id | メッセージ |
+----+-----------------------------------------+
| 1 | はじめまして |
| 2 | 春になったら行きたいところ |
| 3 | ドライブスポット教えて |
+----+-----------------------------------------+
mysql> SELECT * FROM コメント一覧;
+----+-----------------+------------------------------------+------------------+
| id | 投稿一覧_id | コメント 投稿コメント_id
+----+-----------------+------------------------------------+------------------+
| 1 | 1 | よろしくおねがいします | NULL |
| 2 | 1 | はじめまして!! | NULL |
| 3 | 3 | 箱根スカイラインがおすすめ！ | NULL |
| 4 | 3 | 途中、箱根芦ノ湖展望公園に寄ってみて | 3 |
| 5 | 1 | はじめまして！ | 2 |
| 6 | 3 | あそこいいよね！富士山が見える | 4 |
+----+-----------------+-------------------------------------+-----------------+
抽出したいコメントを選んで、UNION ALLを使って並べる
SELECT * FROM コメント一覧 WHERE 投稿コメント_id = 3
UNION ALL
SELECT
コメント一覧.*
FROM
コメント一覧 JOIN (
SELECT * FROM コメント一覧 WHERE 投稿コメント_id = 3
) AS t
ON
コメント一覧.投稿コメント_id = t.id;
+----+-----------------+-----------------------------------------------------+-----------------------+
| id | 投稿一覧_id | コメント | 投稿コメント_id |
+----+-----------------+-----------------------------------------------------+-----------------------+
| 4 | 3 | 途中、箱根芦ノ湖展望公園に寄ってみて | 3 |
| 6 | 3 | あそこいいよね！富士山が見える | 4 |
+----+-----------------+----------------------------------------------------+-----------------------+
2 rows in set (0.01 sec)
補足(MySQL 8.0 から使えるようになったものを紹介)
1. OVERを使ったWindow関数はMySQL 8.0から対応可能となりました。
2. 再帰的ではないCTE(Common Table Expression)
WITH t AS (
SELECT * FROM コメント一覧 WHERE 投稿コメント_id = 3
)
SELECT
コメント一覧.*
FROM
コメント一覧 JOIN t
ON
コメント一覧.投稿コメント_id = t.id;
#20と同じ結果が得られます。
3. 再帰的なCTE
-- 再帰的なCTE WITH RECURSIVE [CTE_Name] AS ( -- 最初に実行する処理 UNION ALL -- 2回目以降に再帰的に実行する処理 ) SELECT * FROM [CTE_Name];
WITH RECURSIVE t AS (
SELECT * FROM コメント一覧 WHERE 投稿コメント_id = 3
UNION ALL
SELECT
コメント一覧.*
FROM
コメント一覧 JOIN t
ON
コメント一覧.投稿コメント_id = t.id
)
SELECT * FROM t;
CTEを用いでSQLを書くことで、コメント一覧テーブルに新しいコメントが付いても、そのデータをちゃんと抽出することができる
#おわりに
文法は間違っていないのに、syntax errorが出て動かない。うーん、なんでだ....？
みたいな感じで、少し沼にハマりかけた今回でしたが、よくよくエラー文を確認してみると「MySQLのversionの確認してみ？」的な文章があったので、そこからググると自身の環境ではサポートされていなかったことがわかりました。
エラー文をよく読むようにクセづけていたのでそれが功を奏したのかも知れません。
まだMySQL 8.0のインストールはしていないのですが、MySQL 5.x ではサポートされていなかった機能が多く追加されているので、そのうちインストールしてみようと思います。