Sql Mysql Foreign Keys

MySQLにおける「複数の外部キー」問題の解決：ステップバイステップガイド

データベースを扱うとき、特にMySQLで作業していると、同じ親テーブルを参照する複数の外部キーを実装する必要があるシナリオに直面することがあります。これは特に困難で、例えば、出荷詳細テーブル内で異なる供給業者を関連付けようとする際に、多くのユーザーが直面する問題です。この投稿では、複数の外部キーに関する一般的な質問を探求し、ベストプラクティスを含む体系的な解決策を提供し、トラブルシューティングプロセスを案内します。

問題

SHIPPING_DETAILSテーブルが、配送業者（FedExやUPSなど）と製品取り扱い業者（ダンダー・ミフリンを考えてみてください）の両方を追跡する責任を負っていると想像してみてください。この場合、出荷詳細の複数の列が同じ親テーブルであるVENDORを参照する必要があります。しかし、これらの列に対して外部キー制約を作成しようとすると、複数の外部キーを定義できないといった問題が発生することがあります。

ユーザーはしばしば次のようなエラーに直面します：

Can't create table './<DB_NAME>/SHIPPING_GRID.frm' (errno: 150)

このエラーは通常、外部キーの定義または関連するデータ型に問題があることを示しています。

解決策概観

MySQLで複数の外部キーを定義する問題を解決するために、次のステップに従ってください：

  1. テーブル構造を正しく定義する: VENDORテーブルに外部キー列が互換性のあるユニークな主キーがあることを確認してください。

  2. テーブルタイプを修正する: 両方のテーブル（VENDORおよびSHIPPING_GRID）がInnoDBタイプであることを確認してください。これは、他のストレージエンジン（MyISAMなど）では外部キー制約がサポートされていないため、重要です。

  3. データ型の互換性を確保する: 外部キーのデータ型が参照元の主キーのデータ型と一致していることを確認してください。たとえば、VENDOR.noがINT(6)として定義されている場合、すべての外部キー参照も同じタイプでなければなりません。

  4. 重複した主キー定義を避ける: SQL文に誤って複数の主キー定義が含まれていないことを確認してください。これにより、テーブル作成中にエラーが発生する可能性があります。

例コード

以下は、上記のポイントを考慮した後、SHIPPING_GRIDテーブルを正しく定義する方法の修正された例です：

CREATE TABLE SHIPPING_GRID(  
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '各行のユニークID',
    shipping_vendor_no INT(6) NOT NULL COMMENT '配送業者のためのVENDOR.noへの外部キー（vendors_typeは3でなければならない）',
    start_vendor_no INT(6) NOT NULL COMMENT '出荷元のためのVENDOR.noへの外部キー',
    end_vendor_no INT(6) NOT NULL COMMENT '出荷先のためのVENDOR.noへの外部キー',
    shipment_duration INT(1) DEFAULT 1 COMMENT '出荷にかかる全日数の期間',
    price FLOAT(5,5) NOT NULL COMMENT '出荷1ポンドあたりの価格（小数点以下5桁まで）',
    is_flat_rate TINYINT(1) DEFAULT 0 COMMENT '重量に関係なくフラット料金の場合は1、lbsあたりの価格の場合は0',
    INDEX (shipping_vendor_no),
    INDEX (start_vendor_no),
    INDEX (end_vendor_no),
    FOREIGN KEY (shipping_vendor_no) REFERENCES VENDOR (no),
    FOREIGN KEY (start_vendor_no) REFERENCES VENDOR (no),
    FOREIGN KEY (end_vendor_no) REFERENCES VENDOR (no)  
) ENGINE=InnoDB;

主なポイント

  • データ型が重要: 常に外部キー列が参照する主キーのデータ型と一致していることを確認してください。
  • InnoDBを使用: テーブル間で外部キーを設定する場合、InnoDBエンジンを使用することが不可欠です。
  • 重複を避ける: SQLがエラーを生成している場合、重複した定義や構文の問題がないか再確認してください。

これらのガイドラインに従うことで、複数の外部キーを持つテーブルを効果的に作成し、外部キー制約に関連する一般的な落とし穴を避けることができます。MySQLでの活動に幸運を祈り、楽しいコーディングをお楽しみください！