MySQLで外部キー制約を内部のテーブルに使う場合、どのように書くのか知りたいです

Question

###疑問点
以下はツイッターと同じ仕組みを想定し、parent_tweet_idは返信先のtweet_idを示すカラムになります。
以下でエラーにならないのが疑問です。

###疑問が生じた考え
以下には外部キー制約として

FOREIGN KEY (`parent_tweet_id`) REFERENCES tweets(`tweet_id`)

とありますがこれは

tweetsテーブルを探して、parent_tweet_idと同じ値がtweetsテーブルのtweet_idになければいけません

という意味の制約だと理解しています。

しかしもしこの理解があっている場合、parent_tweet_idとしてINSERTされている999は、tweetsテーブルのtweet_idに存在しないのに、なぜエラーにならないのでしょうか？

またこのように、外部キー制約を内部のテーブルに使う場合、どのように書くのが一般的な処理方法なのでしょうか？

CREATE TABLE tweets(
    `tweet_id` int not null,
    `user_id` int not null,
    `parent_tweet_id` int,
    `content` varchar(1000) not null,
    INDEX idx_parent_tweet_id(parent_tweet_id),
    PRIMARY KEY (`tweet_id`),
    FOREIGN KEY (`parent_tweet_id`) REFERENCES tweets(`tweet_id`)
);
INSERT INTO tweets 
    (`tweet_id`, `user_id`, `parent_tweet_id`, `content`)
VALUES
    (1, 1, 0, '元気ですか？'),
    (2, 2, 999, '元気です') #999は存在しないのにエラーにならない？
;

###考えた処理方法
一般的な処理方法がわからず自分なりに思いついたのは…とても冗長に思えるのですが次の構造です。
つまり返信関係を示すためだけにparent_relationsというテーブルを別に用意するということですが、これはおかしいでしょうか？

CREATE TABLE tweets(
    `tweet_id` int not null,
    `user_id` int not null,
    `content` varchar(1000) not null,
    PRIMARY KEY (`tweet_id`)
);
INSERT INTO tweets 
    (`tweet_id`, `user_id`, `content`)
VALUES
    (1, 1, '元気ですか？'),
    (2, 2, '元気です')
;

CREATE TABLE parent_relations(
    `parent_tweet_id` int not null,
    `tweet_id` int not null,
    PRIMARY KEY (`parent_tweet_id`),
    FOREIGN KEY (`parent_tweet_id`) REFERENCES tweets(`tweet_id`),
    FOREIGN KEY (`tweet_id`) REFERENCES tweets(`tweet_id`)
);
INSERT INTO parent_relations
    (`parent_tweet_id`, `tweet_id`)
VALUES
    (999, 2)
;

Comments

[m.ts10806]

最後のINSERTが文法的におかしいです。調整願います。

[marorin]

バックティック忘れですか？ありがとうございます。直しました。

[hoshi-takanori]

外部キー制約が有効であれば、最初に parent_tweet_id に 0 を指定した時点でエラーになるはずです。
外部キー制約をサポートするストレージエンジン (InnoDB) をお使いでしょうか？
また、foreign_key_checks の値は 1 になってますか？

[marorin]

>外部キー制約をサポートするストレージエンジン (InnoDB) をお使いでしょうか？
どうもありがとうございます。ENGINE=innoDB; とすることでエラーが出ました。
恐れながら引き続き親子関係の構造（tweetsの１つにするか、tweetsとparent_relationsの２つにするか）につきましてもご意見頂戴できましたら幸いです。

[hoshi-takanori]

データベース設計はあまり詳しくありませんが、個人的には最初のやり方 (同じテーブルへの外部キー制約)で充分だと思います。

[marorin]

やはり二番目のやり方は冗長に過ぎますよね。貴重なご意見をどうもありがとうございます。

Answer 1

何れにしても、親子関係を示す状態のナイーブツリーですので、テーブルは分けない方がいいでしょう。
テーブル構造より制約を優先させるのは本末転倒です。制約はロジックで如何様にもなりますし。

それよりも、アンチパターンであるナイーブツリーのを許容するかどうかです。
再帰を採用するならありですけど、そうでないなら経路列挙の構造ですね。

以下参考
SQL: ナイーブツリーと経路列挙モデル
※上記は経路が可変なのでまだアンチパターンですが、上限を決めて固定のカラムにすることがアンチパターンでない方法です。
MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)

経路列挙モデルについて、他のDBMSでは配列を使う方法もあります。

Comments

[marorin]

>テーブル構造より制約を優先させるのは本末転倒です。
優先は制約でなく構造でしたか。どうもありがとうございます。そういったことを知りませんので大変助かります。

>それよりも、アンチパターンであるナイーブツリーのを許容するかどうかです。
こちらも知りませんでした。たしかに取得の際はjoinが増え続けてしまいますし、削除の際は関連性の変更が大きなコストですね。教えて頂き感謝です。

>以下参考
頂戴した参考リンクですと私には難しすぎまして、こちらのリンク（https://qiita.com/hirashunshun/items/06adf4f42f03a9f3b63d）でやっと理解できました。
様々な解決策がありつつ一長一短でもあり、実際に私のケースに使うべきは何が適切な戸惑いますね。
しかしこちらのリンクには「再帰」や「配列を使う」という解決策が見当たりませんでした。そこで下記質問させてくださいませ。

>再帰を採用するならあり
最後に再帰について教えて頂けませんでしょうか。再帰という処理はなんとなくわかるのですが、それをどう採用すると今回のナイーブツリーはありと考えられるのでしょうか。

[marorin]

でもよく考えたらTwitterは以下のように１階層のみ表示するようになっていて、

コメント1
├ 1への返信2
└ 1への返信3

逆に以下のように多階層での表示はしませんから、

コメント1
├ 1への返信2
└ 1への返信3
--└ 3への返信4

こう考えてみますと取得の際にjoinが増え続けるということはない（返信の階層は無際限だが、取得の際は１階層しか取得しない）ので、ナイーブツリーの採用でも問題なさそうに思いました。削除の際も問題ないでしょうし。

と、以上の考え方はあっていますでしょうか。

[sazi]

親と子だけに限定するなら、階層数が可変とはならないので問題は無いでしょう。

[marorin]

コメントありがとうございます。安心できました。

[sazi]

ナイーブツリーの階層のように要素が可変になるものを扱うにはチューリング完全である必要がありますが、
CTE(再帰はこの一部）とwindow機能が実装されるまでSQLはチューリング完全ではありませんでした。

「再帰を採用するならあり」としたのは、再帰を採用しないとSQLのみでは可変に対応できないからです。