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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Laravel

LaravelとはTaylor Otwellによって開発された、オープンソースなPHPフレームワークです。Laravelはシンプルで表現的なシンタックスを持ち合わせており、ウェブアプリケーション開発の手助けをしてくれます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Laravel 5

Laravel 5は、PHPフレームワークLaravelの最新バージョンで、2014年11月に発表予定です。ディレクトリ構造がが現行版より大幅に変更されるほか、メソッドインジェクションやFormRequestの利用が可能になります。

Q&A

解決済

3回答

10449閲覧

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

KIYZ

総合スコア17

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Laravel

LaravelとはTaylor Otwellによって開発された、オープンソースなPHPフレームワークです。Laravelはシンプルで表現的なシンタックスを持ち合わせており、ウェブアプリケーション開発の手助けをしてくれます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Laravel 5

Laravel 5は、PHPフレームワークLaravelの最新バージョンで、2014年11月に発表予定です。ディレクトリ構造がが現行版より大幅に変更されるほか、メソッドインジェクションやFormRequestの利用が可能になります。

0グッド

0クリップ

投稿2018/07/02 10:14

編集2018/07/02 10:53

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

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

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

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

yyyy_mm_dd_hhmmss_create_posts_table.php

php

1<?php 2 3use Illuminate\Support\Facades\Schema; 4use Illuminate\Database\Schema\Blueprint; 5use Illuminate\Database\Migrations\Migration; 6 7class CreatePostsTable extends Migration 8{ 9 public function up() 10 { 11 Schema::create('posts', function (Blueprint $table) { 12 $table->increments('id'); 13 $table->integer('user_id'); 14 $table->string('title'); 15 $table->text('body'); 16 $table->timestamps(); 17 }); 18 } 19 20 public function down() 21 { 22 Schema::dropIfExists('posts'); 23 } 24} 25

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

php

1<?php 2 3use Illuminate\Support\Facades\Schema; 4use Illuminate\Database\Schema\Blueprint; 5use Illuminate\Database\Migrations\Migration; 6 7class CreateTagsTable extends Migration 8{ 9 public function up() 10 { 11 Schema::create('tags', function (Blueprint $table) { 12 $table->increments('id'); 13 $table->string('name')->unique(); 14 $table->timestamps(); 15 }); 16 17 // 中間テーブル 18 Schema::create('post_tag', function (Blueprint $table) { 19 $table->integer('post_id'); 20 $table->integer('tag_id'); 21 $table->primary(['post_id', 'tag_id']); 22 }); 23 } 24 25 public function down() 26 { 27 Schema::dropIfExists('tags'); 28 29 Schema::dropIfExists('post_tag'); 30 } 31} 32

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

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

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

php

1<?php 2 3use Illuminate\Support\Facades\Schema; 4use Illuminate\Database\Schema\Blueprint; 5use Illuminate\Database\Migrations\Migration; 6 7class AddForeignKeyConstraintsToPostTagTable extends Migration 8{ 9 public function up() 10 { 11 Schema::table('post_tag', function (Blueprint $table) { 12 // 外部キー制約 13 $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade'); 14 $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade'); 15 }); 16 } 17 18 public function down() 19 { 20 Schema::table('post_tag', function (Blueprint $table) { 21 Schema::dropIfExists('post_tag'); 22 }); 23 } 24} 25

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

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

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

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

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

投稿2018/07/03 01:44

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

KIYZ

2018/07/03 06:04 編集

ご回答ありがとうございます。 >テーブル単位にしておいたほうが良さそう やはりそうなのですね。今後はそうします。 >登録済みのデータがその外部キー制約違反だから ご指摘ありがとうございます。 以下を試してみましたが、全く同じエラーが発生しました。 ・対象テーブルの全レコードを削除してからマイグレート ・migrate:reset 後、マイグレート 自己解決できましたので、回答を投稿させて頂きました。
sazi

2018/07/03 06:04

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

2018/07/03 06:09

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

0

ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY(参照元カラム名) REFERENCES 参照先テーブル名 (参照先カラム名);

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

追記

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

以下、typoはお許しを

準備

SQL

1CREATE TABLE `parent_table` 2 ( 3 `parent_id` INT NOT NULL, 4 , PRIMARY KEY (`parent_id`) 5 ) 6 ENGINE=InnoDB; 7 8CREATE TABLE `child_table` 9 ( 10 `child_id` INT NOT NULL 11 , `parent_id` INT NOT NULL 12 , PRIMARY KEY (`child_id`) 13 , INDEX `idx_parent_id` (`parent_id`) 14 ) 15 ENGINE=InnoDB ; 16 17-- 初期データ 18INSERT INTO `parent_table`(`parent_id`) VALUES(1) ; 19INSERT INTO `child_table`(`child_id`, `parent_id`) VALUES(1, 1) ; 20INSERT INTO `child_table`(`child_id`, `parent_id`) VALUES(2, 3) ; -- 親テーブルに存在しないデータ 21COMMIT ;

外部キーを追加

SQL

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

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

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

SQL

1SELECT CT.`child_id`, CT.`parent_id` 2FROM `child_table` CT 3WHERE CT.`parent_id` NOT IN 4 ( 5 SELECT PT.`parent_id` FROM parent_table` PT 6 )

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

SQL

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

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

SQL

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

投稿2018/07/02 10:36

編集2018/07/02 23:15
Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

KIYZ

2018/07/02 10:45

ご回答ありがとうございます。 その点は間違っていないと思います。 $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade'); $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
Orlofsky

2018/07/02 10:49

[MySQL]タグは追加しましょう。 通常、テーブルの定義関係のSQLを実行するのにいちいちPHPを書いたりしないで、コマンドラインから実行します。 http://sugamasao.hatenablog.com/entry/20070827/1188236439 外部制約違反になるデータが存在しませんか?
KIYZ

2018/07/02 11:17 編集

タグを追加しました。ご指摘ありがとうございます。 質問の意図から外れてしまいますが、: >コマンドラインから実行します その方法には、いつでもテーブル定義の状態を以前の(特定の)状態に戻せる状態を維持しておくこと以上のメリットがあるのでしょうか。 >新たなマイグレーションファイルを作成・マイグレートする方法を学びたいと考えています。 新規マイグレーションファイルを作成して外部キー制約を定義する方法をご教授頂けると幸いです。 >外部制約違反になるデータが存在しませんか? 申し訳ありませんが、自分には分かりかねます。
Orlofsky

2018/07/02 11:36

ALTER TABLE ... DROP CONSTRAINT ... で外部制約を削除できます。 そのためにマイグレーションファイルをいちいち作ったりしません。CREATE TABLE文などのSQLファイルは保存しますが。 外部参照キーを追加するには、参照元のテーブルのキーが参照先のテーブルのキーに登録されている必要があります。 適切なコメントを希望されるなら、[質問]に2つのテーブルのCREATE TABLE文を載せては?
KIYZ

2018/07/02 12:46 編集

>参照元のテーブルのキーが参照先のテーブルのキーに登録されている必要があります 「登録」の意味が理解できないのですが、よろしければご教授頂けないでしょうか。 >2つのテーブルのCREATE TABLE文を載せて 質問に二つのテーブルを作成するために書いたマイグレーションファイルのコードを載せているのですが、それでは不十分だったのでしょうか。もしそうなのであれば申し訳ありません。 ・yyyy_mm_dd_hhmmss_create_posts_table.php ・yyyy_mm_dd_hhmmss_create_tags_table.php しかし、テーブル作成時には上記ファイルに書いたコードしか使っていないため、他にどのコードをお見せするべきなのかが分かりません。
Orlofsky

2018/07/02 23:16

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

2018/07/03 00:11

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

0

自己解決

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

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

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

投稿2018/07/03 05:55

KIYZ

総合スコア17

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問