外部キー制約を用いて、2つの列を外部参照したい

Question

前提・実現したいこと

filesテーブルのuser_id列とuser_name列から、それぞれusersテーブルのid列とname列を参照したい。
files（子テーブル）　　　　　users（親テーブル）
・user_id（外部キー）　　　　・id（参照されるキー）
・user_name（外部キー）　　　・name（参照されるキー）

発生している問題・エラーメッセージ

ERROR 3780 (HY000): Referencing column 'user_name' and referenced column 'name' in foreign key constraint 'files_ibfk_1' are incompatible.

該当のソースコード

CREATE TABLE files (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    user_id int unsigned,
    user_name varchar(191) NULL,
    FOREIGN KEY (user_id, user_name)
         REFERENCES users(id,name)
);

試したこと

mysql> SHOW CREATE TABLE users;
で調べてみると、
| users | CREATE TABLE users (
id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
email varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
email_verified_at timestamp NULL DEFAULT NULL,
password varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
remember_token varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY users_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
でした。
usersテーブルのnameも、filesテーブルのuser_nameもvarchar(191)なのに、なぜ参照できないのでしょうか。

補足情報（FW/ツールのバージョンなど）

Laravel Framework 5.7.29
mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

Answer 1

usersテーブルのnameも、filesテーブルのuser_nameもvarchar(191)なのに、なぜ参照できないのでしょうか。

FOREIGN KEY (user_id, user_name)
         REFERENCES users(id,name)

上記で指定しているREFERENCES users(id,name)のid,nameの項目のインデックスがusersでは定義されていないからでしょう。
13.1.17.2 外部キー制約の使用

ですが、usersに対して外部キー制約をするというなら、usersのprimaryKey(ID)に対して行うべきであり、そもそもfilesにuser_nameという項目を持つべきではありません。

filesにuser_nameという項目を持つというなら、それはfilesにデータを作成する際にusersを参照した結果というだけであり変更も可能なもので、外部キー制約するものではないのです。

Comments

[ShieNishikawa]

id,nameの項目のインデックスを定義したら、外部キー制約できました。しかし、ウェブサイトのフォームからfileをpostしようとすると、「user_name dosen't exist」となりました。

なぜこのような構成にしようとしたかというと、fileをpostしたuserのsort機能を付けたかったからです。user_idでsortするとregisterした順に並んでしまうので、アルファベット順に並べるためにuser_nameという項目を設けてsortしようとしたのでした。そのためには、2つの組で外部キー制約するという方法しか思いつきませんでした。正規化していないおかしな構成でした。
結局、$user = Auth::user(); user_name = $user->name でユーザ名を取得することにしました。

浅はかな質問に丁寧に回答していただき、ありがとうございました。外部キー制約について大変勉強になりました。

[sazi]

ソートするのが目的で、外部キー制約を付ける事などありません。
単にソート項目を指定すれば良いだけです。
ソートによる性能が問題なら、目的に応じたインデックスの追加を行います。
ただ、名称によるソートという事なら基本文字コードによるソートですので、意図した並びでない場合もあります。
照合順序も確認された方が良いかもしれません。
https://www.dbonline.jp/mysql/ini/index5.html

[ShieNishikawa]

ありがとうございます。確認してみます。

Answer 2

そもそも、なぜそのような構成が必要なのでしょうか？

正規化という観点から考えれば、user_idで紐付けることで、そこから名前は取り出せますので、user_nameまで関連付けるのは全く冗長なだけです。

Comments

[ShieNishikawa]

fileをpostしたuserのsort機能を付けたかったからです。user_idでsortするとregisterした順に並んでしまうので、アルファベット順に並べるためにuser_nameという項目を設けてsortしようとしたのでした。そのためには、2つの組で外部キー制約するという方法しか思いつきませんでした。正規化していないおかしな構成でした。
結局、$user = Auth::user(); user_name = $user->name; でユーザ名を取得することにしました。
正規化について指摘していただき、ありがとうございました。

[maisumakun]

> アルファベット順に並べるためにuser_nameという項目を設けてsortしようとしたのでした。そのためには、2つの組で外部キー制約するという方法しか思いつきませんでした。

JOINすれば、別テーブルを基準に並べ替えることも可能です。

[ShieNishikawa]

ありがとうございます。検討してみます。