質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.10%

DBテーブルに後から外部キー制約を定義する方法

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,136

KIYZ

score 17

前提

学習目的で Laravel を使ってブログサイトを開発しており、現在は投稿にタグを付けたり、表示する投稿をタグで絞り込んだりする機能を実装しているところで、そのために Post モデルと Tag モデルにリレーションを定義し、それぞれのモデルのテーブルとそれらの中間テーブルを作成しました。

データベース管理にはマイグレーション機能を使用しています。

中間テーブル作成時に外部キー制約を定義していなかったことが原因で、Post または Tag のレコードを削除した時、存在しなくなった post_id や tag_id との接続を保持する"残すべきではないレコード"が中間テーブルに残ってしまう状態になっているため、中間テーブルに外部キー制約を定義しようとしています。

現状のマイグレーションファイル:

yyyy_mm_dd_hhmmss_create_posts_table.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration
{
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id');
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

Q1. 下記ファイル内で tags テーブルと 中間テーブルの post_tag テーブルをまとめて定義しました。
このような場合、マイグレーションファイルの内容をファイル名から判断しやすくするため?に、別々のファイルに分けるべきなのでしょうか。
yyyy_mm_dd_hhmmss_create_tags_table.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTagsTable extends Migration
{
    public function up()
    {
        Schema::create('tags', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->timestamps();
        });

        // 中間テーブル
        Schema::create('post_tag', function (Blueprint $table) {
            $table->integer('post_id');
            $table->integer('tag_id');
            $table->primary(['post_id', 'tag_id']);
        });
    }

    public function down()
    {
        Schema::dropIfExists('tags');

        Schema::dropIfExists('post_tag');
    }
}

知りたいこと

Q2. 中間テーブルを(定義)作成後、後から外部キー制約を定義するにはどうすれば良いのでしょうか。
「テーブル定義の変更履歴は残していくべき」ということを学んだことがあるため、マイグレーションをロールバックしてやり直すという方法ではなく、新たなマイグレーションファイルを作成・マイグレートする方法を学びたいと考えています。

ご教授のほどよろしくお願い申し上げます。

試したこと

doctrine/dbal を導入後、下記コマンド
php artisan make:migration add_foreign_key_constraints_to_post_tag_table --table=post_tag
で新規マイグレーションファイルを生成し、それを下記のように編集し、

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddForeignKeyConstraintsToPostTagTable extends Migration
{
    public function up()
    {
        Schema::table('post_tag', function (Blueprint $table) {
            // 外部キー制約
            $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::table('post_tag', function (Blueprint $table) {
            Schema::dropIfExists('post_tag');
        });
    }
}


マイグレートした結果、以下のようなエラーが発生しました。

In Connection.php line 647:

  SQLSTATE[HY000]: General error: 1005 Can't create table 'blog.#sql-c41_b5' (errno: 150) (SQL: alter   
  table `post_tag` add constraint `post_tag_post_id_foreign` foreign key (`post_id`) references `posts  
  ` (`id`) on delete cascade)                                                                           


In PDOStatement.php line 107:

  SQLSTATE[HY000]: General error: 1005 Can't create table 'blog.#sql-c41_b5' (errno: 150)  


In PDOStatement.php line 105:

  SQLSTATE[HY000]: General error: 1005 Can't create table 'blog.#sql-c41_b5' (errno: 150) 

環境

PHP 7.0.30
Laravel 5.4
DB: MySQL

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+2

https://teratail.com/questions/63031">ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY(参照元カラム名) REFERENCES 参照先テーブル名 (参照先カラム名);

参照元カラムと参照先カラムの定義が違っていたりしませんか?

追記

外部キーは親子関係にあるテーブルを指定することで、親に存在しないデータを
子テーブルに入れさせないことでイレギュラーなデータを防ぐ機能です。

以下、typoはお許しを

準備

CREATE TABLE `parent_table`
    (
    `parent_id` INT NOT NULL,
  , PRIMARY KEY (`parent_id`)
    )
    ENGINE=InnoDB;

CREATE TABLE `child_table`
    (
    `child_id` INT NOT NULL
  , `parent_id` INT NOT NULL
  , PRIMARY KEY (`child_id`)
  , INDEX `idx_parent_id` (`parent_id`)
    )
    ENGINE=InnoDB ;

-- 初期データ
INSERT INTO `parent_table`(`parent_id`) VALUES(1) ;
INSERT INTO `child_table`(`child_id`, `parent_id`) VALUES(1, 1) ;
INSERT INTO `child_table`(`child_id`, `parent_id`) VALUES(2, 3) ;  -- 親テーブルに存在しないデータ
COMMIT ;


外部キーを追加

ALTER TABLE `child_table`
    ADD CONSTRAINT `parent_child_parent_id_fk` FOREIGN KEY `parent_id` REFERENCES `parent_table` (`parent_id`)


と、親テーブルに存在しないデータがあるから、エラーになって外部キーを追加できない。

副問合せで親キーが見つからないデータを特定する。

SELECT CT.`child_id`, CT.`parent_id`
FROM `child_table` CT
WHERE CT.`parent_id` NOT IN
    (
    SELECT PT.`parent_id` FROM parent_table` PT
    )


で、親データを追加するか、間違った小データをDELETEする。

DELETE FROM `child_table` CT
WHERE CT.`child_id` = 2 ;
COMMIT ;


改めて外部キーを追加する。

ALTER TABLE `child_table`
    ADD CONSTRAINT `parent_child_parent_id_fk` FOREIGN KEY `parent_id` REFERENCES `parent_table` (`parent_id`)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/02 21:45 編集

    >参照元のテーブルのキーが参照先のテーブルのキーに登録されている必要があります
    「登録」の意味が理解できないのですが、よろしければご教授頂けないでしょうか。

    >2つのテーブルのCREATE TABLE文を載せて
    質問に二つのテーブルを作成するために書いたマイグレーションファイルのコードを載せているのですが、それでは不十分だったのでしょうか。もしそうなのであれば申し訳ありません。

    ・yyyy_mm_dd_hhmmss_create_posts_table.php
    ・yyyy_mm_dd_hhmmss_create_tags_table.php

    しかし、テーブル作成時には上記ファイルに書いたコードしか使っていないため、他にどのコードをお見せするべきなのかが分かりません。

    キャンセル

  • 2018/07/03 08:16

    回答を追記しました。
    SQL入門はきちんと勉強してください。

    キャンセル

  • 2018/07/03 09:11

    >Orlosfsky さん
    SQLの直書きじゃ、そもそもマイグレーションじゃなくなってしまいます。

    キャンセル

+2

詳しくないので外していたらごめんなさい

Q1
テーブル単位にしておいたほうが良さそうですよ。
Laravel 5.5 で多対多(Many To Many)リレーションを使ってタグ付き掲示板を作る
Laravelで多対多の中間テーブルを用いたDBの作り方と使い方

Q2
登録済みのデータがその外部キー制約違反だからってことは無いですか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/03 15:01 編集

    ご回答ありがとうございます。

    >テーブル単位にしておいたほうが良さそう
    やはりそうなのですね。今後はそうします。

    >登録済みのデータがその外部キー制約違反だから
    ご指摘ありがとうございます。
    以下を試してみましたが、全く同じエラーが発生しました。

    ・対象テーブルの全レコードを削除してからマイグレート
    ・migrate:reset 後、マイグレート

    自己解決できましたので、回答を投稿させて頂きました。

    キャンセル

  • 2018/07/03 15:04

    データを残しつつ行いたいのかと思ってました。可能なら当然リセットしたほうが問題は少なくて済みますね。

    キャンセル

  • 2018/07/03 15:09

    質問文が説明不足だったようですね。申し訳ありません。

    キャンセル

check解決した方法

0

外部キー制約追加用のマイグレーションファイルをマイグレートした時に発生するエラーは、
テーブル作成時に対象のカラムにインデックスを作成していなかったことが原因のようでした。

対象のカラムにインデックスを追加すると、外部キー制約追加用のマイグレーションファイルをマイグレートすることに成功しました。

参考にしたページ:
https://qiita.com/kidach1/items/1060e094a0b0f9b9eaaa

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.10%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる