とある日

応用情報技術者試験の問題を見ていてふと、疑問に思ったテーブル構造があった。

テーブル構造しか説明しないので詳細が知りたい方は平成３０年秋期午後問６を参照してください。

実表名	列名
社員	社員ID,氏名,所属組織ID...
組織	組織ID,組織名,組織長の社員ID,上位組織の組織ID

問題を見た感じ外部キーについての記述は見られないが、おそらくこの表同士で外部キーを設定していると思った。

社員.所属組織ID → 組織.組織ID
組織.組織長の社員ID → 社員.社員ID
組織.上位組織の組織ID → 組織.組織ID

2つの表同士の外部キーを設定したことがないので試しにやってみようかと思います。

２つの表同士の外部キーの制約設定を試しにやってみようかと思います。

さらに、このテーブル設計ってどうなの？と思うところがあるので解説しつつ自分の設計を記述していきたいと思います。

環境

項目	詳細
DataBase	MySQL 8.0.23
OS	CentOS8

詳しくは下記のテスト環境の記事を参照してください。

updraft.hatenadiary.com

テーブル定義

※今回の場合は、外部キーを設定する列や各データの識別をわかりやすくするための列だけ設けています。

社員

社員データを保持するテーブル。

syozoku_sosiki_idは、所属する組織IDを保持する列。

mysql> show create table syain\G;
*************************** 1. row ***************************
       Table: syain
Create Table: CREATE TABLE `syain` (
  `syain_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `syozoku_sosiki_id` int DEFAULT NULL,
  PRIMARY KEY (`syain_id`),
  KEY `fk_syain` (`syozoku_sosiki_id`),
  CONSTRAINT `syain_ibfk_1` FOREIGN KEY (`syozoku_sosiki_id`) REFERENCES `sosiki` (`sosiki_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

組織

sosikityou_syain_id：各組織には、組織長が存在するのでその社員ID

jyoui_syozoku_sosiki_id：各組織はツリー構造のものが存在するようです。

mysql> show create table sosiki\G;
*************************** 1. row ***************************
       Table: sosiki
Create Table: CREATE TABLE `sosiki` (
  `sosiki_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sosikityou_syain_id` int DEFAULT NULL,
  `jyoui_syozoku_sosiki_id` int DEFAULT NULL,
  PRIMARY KEY (`sosiki_id`),
  KEY `fk_sosiki_syain` (`sosikityou_syain_id`),
  KEY `fk_sosiki_sosiki` (`jyoui_syozoku_sosiki_id`),
  CONSTRAINT `sosiki_ibfk_1` FOREIGN KEY (`sosikityou_syain_id`) REFERENCES `syain` (`syain_id`),
  CONSTRAINT `sosiki_ibfk_2` FOREIGN KEY (`jyoui_syozoku_sosiki_id`) REFERENCES `syain` (`syain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

テーブル作成手順

上記のテーブル作成を順を追って書いていきます。

FOREIGN_KEY_CHECKS

まずは、外部キーについてのシステム変数を確認。

外部キーについての検証をするので外部キーのシステム変数が有効でないと意味がないので有効かの確認。

www.dondari.com

dev.mysql.com

mysql> select @@FOREIGN_KEY_CHECKS;
+----------------------+
| @@FOREIGN_KEY_CHECKS |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

CREATE TABLE

外部キーの設定はあとで追加設定します。

型などは適当に設定しています。

create table syain (
    syain_id int primary key auto_increment,
    name varchar(20),
    syozoku_sosiki_id int 
);
create table sosiki (
    sosiki_id int primary key auto_increment,
    name varchar(20),
    sosikityou_syain_id int,
    jyoui_syozoku_sosiki_id int 
);

ALTER TABLE

外部キー制約を追加していきます。

ALTER TABLE syain ADD FOREIGN KEY fk_syain(syozoku_sosiki_id) REFERENCES sosiki(sosiki_id);
ALTER TABLE sosiki ADD FOREIGN KEY fk_sosiki_syain(sosikityou_syain_id) REFERENCES syain(syain_id);
ALTER TABLE sosiki ADD FOREIGN KEY fk_sosiki_sosiki(jyoui_syozoku_sosiki_id) REFERENCES syain(syain_id);

社員.所属組織ID → 組織.組織ID

ALTER TABLE syain ADD FOREIGN KEY fk_syain(syozoku_sosiki_id) REFERENCES sosiki(sosiki_id);
組織.組織長の社員ID → 社員.社員ID

ALTER TABLE sosiki ADD FOREIGN KEY fk_sosiki_syain(sosikityou_syain_id) REFERENCES syain(syain_id);
組織.上位組織の組織ID → 組織.組織ID

ALTER TABLE sosiki ADD FOREIGN KEY fk_sosiki_sosiki(jyoui_syozoku_sosiki_id) REFERENCES syain(syain_id);

INSERT INTO

適当なデータを挿入します。

insert into syain (name) values("mysql");
insert into syain (name) values("mariadb");
insert into syain (name) values("oracle");
insert into syain (name) values("mongodb");

insert into sosiki (name) values("RDBMS");
insert into sosiki (name) values("NoSQL");
insert into sosiki (name) values("DB");

UPDATE SET

update sosiki set sosikityou_syain_id = 1 where name = 'RDBMS';
update sosiki set sosikityou_syain_id = 4 where name = 'NoSQL';
update sosiki set jyoui_syozoku_sosiki_id = 3 where name != 'DB';

update syain set syozoku_sosiki_id = 2 where name = 'mongodb';
update syain set syozoku_sosiki_id = 1 where name != 'mongodb';

SELECT

mysql> select * from syain;
+----------+---------+-------------------+
| syain_id | name    | syozoku_sosiki_id |
+----------+---------+-------------------+
|        1 | mysql   |                 1 |
|        2 | mariadb |                 1 |
|        3 | oracle  |                 1 |
|        4 | mongodb |                 2 |
+----------+---------+-------------------+
4 rows in set (0.00 sec)

mysql> select * from sosiki;
+-----------+-------+---------------------+-------------------------+
| sosiki_id | name  | sosikityou_syain_id | jyoui_syozoku_sosiki_id |
+-----------+-------+---------------------+-------------------------+
|         1 | RDBMS |                   1 |                       3 |
|         2 | NoSQL |                   4 |                       3 |
|         3 | DB    |                NULL |                    NULL |
+-----------+-------+---------------------+-------------------------+
3 rows in set (0.00 sec)

外部キーの問題

この外部キー制約やテーブル設計に不信感を抱いた理由としては、”データ削除する際にどうするのだろう”ということ。

外部キー制約は、親と子の関係としてテーブルを関連付け、データを削除する際は、子からじゃないとデータを削除できない条件がある制約となっています。

このテーブルの場合は、どちらとも親であり子であるといった状態になっています。

試しに各データを削除してみます。

念の為トランザクションを開始してロールバックできるようにしておきます。

社員テーブルデータ削除

mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from syain where syain_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hobby`.`sosiki`, CONSTRAINT `sosiki_ibfk_1` FOREIGN KEY (`sosikityou_syain_id`) REFERENCES `syain` (`syain_id`))

組織テーブルデータ削除

mysql> delete from sosiki where sosiki_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hobby`.`syain`, CONSTRAINT `syain_ibfk_1` FOREIGN KEY (`syozoku_sosiki_id`) REFERENCES `sosiki` (`sosiki_id`))

どちらのデータ削除にしても片方のテーブルで参照している行はエラーになります。

テーブル再設計

上記の外部キーの削除の点に関して、不自由があるなと思うので、自分が思うテーブル設計を考えていきたいと思います。

※あくまで要件によってテーブル設計は変わるので今回のは個人的意見です。

組織長を別のテーブルに切り分けて作成しました。

これによって、親同士でデータが削除できないことはなくなると思います。

f:id:Updraft:20210331010342p:plain

〆

応用情報技術者試験ドットコムの掲示板を見てて気になった問題について自分の考えを記述してみました。

テーブル設計について他の方の意見とかも聞いてみたいですね。

テーブル設計は、要件によって千差万別なので一概にこれが正解とかはないとは思いますが、テーブル設計について考えているときがDBで一番難しくて楽しいと感じる瞬間ですね。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、外部キー制約参照同士テーブルの日。

目次