複合主キーを外部キーとした複合主キーのテーブルを作ろうとすると外部キー制約エラーが発生する

Question

前提

初質問なので不足情報あればぜひとも教えてください。

phpmyadminでmysqlを使用してテーブルを作成しようとしていますが、外部キー制約にエラーが出ます。

実現したいこと

• 複合主キーを外部キーとした複合主キーのテーブルを作る

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

#1215 - Cannot add foreign key constraint

該当のソースコード

CREATE TABLE customers(
    customers_mail VARCHAR(191) PRIMARY KEY,
    customers_pass VARCHAR(191) NOT NULL,
    customers_nick VARCHAR(191) NOT NULL,
    customers_ln VARCHAR(191) NOT NULL,
    customers_fn VARCHAR(191) NOT NULL,
    customers_postcode CHAR(7) NOT NULL,
    customers_pref VARCHAR(191) NOT NULL,
    customers_address VARCHAR(191) NOT NULL,
    customers_subaddress VARCHAR(191)
);
CREATE TABLE makers(
    makers_id INT PRIMARY KEY,
    makers_name VARCHAR(191) NOT NULL
);
CREATE TABLE buys(
    buys_id INT PRIMARY KEY,
    buys_date DATE NOT NULL,
    customers_mail VARCHAR(191) NOT NULL,
    buys_payment INT NOT NULL,
    FOREIGN KEY fkey1 (customers_mail) REFERENCES customers(customers_mail)
);
CREATE TABLE items(
    items_id INT PRIMARY KEY,
    makers_id INT NOT NULL,
    items_name VARCHAR(191) NOT NULL,
    items_description VARCHAR(191) NOT NULL,
    items_price INT NOT NULL,
    items_url VARCHAR(191) NOT NULL,
    items_genre VARCHAR(191) NOT NULL,
    items_material VARCHAR(191) NOT NULL,
    items_color VARCHAR(191) NOT NULL,
    FOREIGN KEY fkey2 (makers_id) REFERENCES makers(makers_id)
);
CREATE TABLE itemsinfo(
    items_id INT,
    itemsinfo_size VARCHAR(191),
    itemsinfo_stock INT NOT NULL,
    FOREIGN KEY fkey3 (items_id) REFERENCES items(items_id),
    PRIMARY KEY(items_id, itemsinfo_size)
);
CREATE TABLE details(
    buys_id INT,
    items_id INT,
    itemsinfo_size VARCHAR(191),
    details_vol INT NOT NULL,
    FOREIGN KEY fkey4 (buys_id) REFERENCES buys(buys_id),
    FOREIGN KEY fkey5 (items_id) REFERENCES itemsinfo(items_id),
    FOREIGN KEY fkey6 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),
    PRIMARY KEY(buys_id, items_id, itemsinfo_size)
);

試したこと

参照元が主キーである（＝一意制約）ことを確認
参照元と参照先の型が同じであることを確認

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

lolipopのphpmyadminです。

xamppのphpmyadminで試した結果、次のエラーとログが出力されました。

#1005 - `webdb`.`details` テーブルが作れません.(errno: 150 "Foreign key constraint is incorrectly formed")

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-10-29 14:11:39 0x3600 Error in foreign key constraint of table `webdb`.`details`:
FOREIGN KEY fkey6 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),
    PRIMARY KEY(buys_id, items_id, itemsinfo_size)
):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Create  table `webdb`.`details` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY fkey6 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),
    PRIMARY KEY(buys_id, items_id, itemsinfo_size)
)'.

Comments

[Orlofsky]

質問は修正できます。

>外部キー制約にエラーが出ます。
質問のCREATE TABLE文を実行した時のエラーメッセージを一切編集しないでそのまま追加しては？

[mitsumitsu]

エラーメッセージは#1215 - Cannot add foreign key constraintと表示されるのみです...

Answer 1

CREATE TABLE itemsinfo(
...
    PRIMARY KEY(items_id, itemsinfo_size)

と、PRIMARY KEYが2つの列が指定してあるのに、
...
CREATE TABLE details(
    FOREIGN KEY fkey6 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),
外部キーが1つの列しか設定していないからエラーになっています。

制約名は分かり易いネーミングに。

なお、DWHでは遅くなるから外部キーは設定しません。外部キーがなくても整合性が維持できる能力のある人材を使います。

Comments

[mitsumitsu]

すみません、もう少し詳しく教えていただけますか
>外部キーが1つの列しか設定していないからエラーになっています。
に関してですが、外部キーは主キー同様
FOREIGN KEY fkey5 (items_id) REFERENCES itemsinfo(items_id),
FOREIGN KEY fkey6 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),
と二つ設定している（つもり）です。
こういうことではないということでしょうか。
見当違いかもしれませんが、上記の回答をうけ次のように修正して再度実行してみましたところ、エラーにはなりませんでしたがそもそもitems_idとitemsinfo_sizeの参照がされませんでした（無視された？）
FOREIGN KEY fkey5 (items_id, itemsinfo_size) REFERENCES itemsinfo(items_id, itemsinfo_size),

[Orlofsky]

答えは回答に書きました。ちゃんと読んでください。

FOREIGN KEY fkey5 (items_id, itemsinfo_size) REFERENCES itemsinfo(items_id, itemsinfo_size),
って試しましたか？

シリコンバレーで作られたOracle Databaseの銀行用のパッケージで闇雲に外部キーを設定してあったシステムの日本の環境に移行する案件をやったことがあります。パフォーマンスが悪くてかなりの外部キーを外すように提案しました。

[mitsumitsu]

ええ、２つ前のコメントであなたが先程されたコメント通りの記述を行い実行しましたが、先述したようにエラーは無くなりましたが外部キー制約の効果がありませんでした。

[Orlofsky]

質問は修正できます。
実行したSQLとそこで表示されるエラーメッセージをそのままで提示しては？

[mitsumitsu]

もしかして非日本語話者の方でしょうか？
前述した通り、実行したsqlはあなたが提示した通りのものですし、エラーメッセージは出ませんでした。
しかし当該項目に外部キー制約が付与されませんでした。

[Orlofsky]

あなたの日本語理解力がないだけです。
面倒だからこれ以上はコメントしません。

[mitsumitsu]

teratailこわ…

Answer 2

すべて解決するわけではないですが、とりあえず以下
・外部キー名の重複（同一DB内で一意である必要がある）
マジックナンバーじゃなくfk_テーブル名_カラム名_・・・・みたいな命名の方が良いと思います

FOREIGN KEY fkey2 (items_id) REFERENCES itemsinfo(items_id),
FOREIGN KEY fkey3 (itemsinfo_size) REFERENCES itemsinfo(itemsinfo_size),

どちらもPKではないような。

Comments

[m.ts10806]

phpMyAdminならテーブルなりカラムなりGUIで設定できる機能があるので、
そちらを使った方が確実だと思います。

[mitsumitsu]

>どちらもPKではないような。
どちらの参照元にはprimary keyを付与していて、GUI上でも鍵マークが灰色になっているのですがそれは何か間違っているのでしょうか

[m.ts10806]

失礼
カラム 型 PRIMARY KEY
と書いていたり
PRIMARY KEY() とまとめていたりで統一されてないことで混乱していました。

テーブル作る順序とかも問題ないですか？

[mitsumitsu]

統一性に関してはすみません...
テーブルの順序は問題ないはずです、参照される順に記述しているので

ちなみに、GUI上でdetailsのitemsinfo_sizeに外部キー制約付与しようとすると、選択肢にitemsinfoのitemsinfo_sizeが表示されないんですよね
sqlでエラーが出る項目なのでそりゃそうなのですが

Answer 3

FOREIGN KEY fkey5 (items_id, itemsinfo_size) REFERENCES itemsinfo(items_id, itemsinfo_size),

としました。どうもGUI上ではリレーションが設定されていないような表示がされるのですが、実際にinsert等を行ってみるとしっかりと制約がかかっていました。

ご協力ありがとうございました。