【トラブル解決】MySQLで外部キーと非ユニークインデックスを使う際のエラーとその解決方法
2024-07-27
MySQLで外部キーが非ユニークインデックスを参照できるかどうか
MySQLでは、外部キーはユニークインデックスだけでなく非ユニークインデックスも参照できます。ただし、いくつかの制限事項と注意事項があります。
データベースにおける外部キーは、異なるテーブル間の関連性を定義する制約です。外部キーは、あるテーブルの列（子キー）を、別のテーブルの列（親キー）と関連付けます。
ユニークインデックスは、各行が異なる値を持つことを保証するインデックスです。一方、非ユニークインデックスは、同じ値を持つ行が複数存在することを許容します。
MySQLにおける外部キーと非ユニークインデックス
MySQLでは、外部キーはデフォルトで親キーのユニークインデックスを参照します。しかし、FOREIGN KEY制約にREFERENCES句のUSINGオプションを使用することで、非ユニークインデックスを参照することも可能です。
例
CREATE TABLE parent (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id) USING INDEX parent_idx
);
CREATE INDEX parent_idx ON parent (name);
制限事項
- 親キー列がNULL値を許容する場合、外部キーは非ユニークインデックスを参照できません。
- 子キー列は、親キー列と同じ順序でなければなりません。
注意事項
- 外部キーが非ユニークインデックスを参照する場合、親キーの更新や削除によって子キーの参照整合性が損なわれる可能性があります。
- 外部キーが非ユニークインデックスを参照する場合、パフォーマンスが低下する可能性があります。
- 上記はMySQL 8.0の情報に基づいています。他のバージョンのMySQLでは、異なる動作をする可能性があります。
-- テーブル作成
CREATE TABLE parent (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id) USING INDEX parent_idx
);
CREATE INDEX parent_idx ON parent (name);
-- データ挿入
INSERT INTO parent (name) VALUES ('John Doe');
INSERT INTO parent (name) VALUES ('Jane Doe');
INSERT INTO child (parent_id) VALUES (1);
INSERT INTO child (parent_id) VALUES (2);
-- 親キーの更新
UPDATE parent SET name = 'John Smith' WHERE id = 1;
-- 子キーの参照
SELECT * FROM child;
-- 結果
-- id | parent_id
-- --- | --------
-- 1 | 1
-- 2 | 2
parentテーブルのid1の行のname列をJohn Smithに更新すると、childテーブルのparent_id列は影響を受けません。これは、parent_idxインデックスはname列に基づいているためです。
実行結果
-- id | parent_id
-- --- | --------
-- 1 | 1
-- 2 | 2
- 上記は単純な例です。実際のアプリケーションでは、より複雑な外部キー制約を使用する可能性があります。
外部キーが非ユニークインデックスを参照する他の方法
別名を使用する
CREATE TABLE parent (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id)
);
ALTER TABLE child
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id)
REFERENCES parent(id)
USING INDEX parent_idx;
サブクエリを使用する
CREATE TABLE parent (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id)
REFERENCES (SELECT id FROM parent WHERE name = 'John Doe')
);
- 別名を使用する方法とサブクエリを使用する方法は、
USING INDEXオプションよりも複雑です。 - サブクエリを使用する方法は、パフォーマンスが低下する可能性があります。
mysql sql foreign-keys
