时间戳

首页 > 解决方案 > Laravel 使用迁移时显示 1215（无法添加外键约束）：通过 ssh 刷新

php - Laravel 使用迁移时显示 1215（无法添加外键约束）：通过 ssh 刷新

问题描述

Laravel 5.6.38

MySQL 5.7.23

PHP v7.2.10

在 localhost PHP 7.2.4 中，它在 localhost 中工作正常，但在生产中显示以下错误。

php artisan migrate:fresh
Dropped all tables successfully.
Migration table created successfully.

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `videos` add constraint `videos_video_status_id_foreign` foreign key (`video_status_id`) references `statuses` (`status_id`))

  at /var/www/html/myapp/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /var/www/html/myapp/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /var/www/html/myapp/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  Please use the argument -v to see more details.

这就是它在 localhost 中的运行方式

D:\work\www\myapp>php artisan migrate:fresh
Dropped all tables successfully.
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2018_08_29_112331_entrust_setup_tables
Migrated:  2018_08_29_112331_entrust_setup_tables
Migrating: 2018_08_31_103313_create_audits_table
Migrated:  2018_08_31_103313_create_audits_table
Migrating: 2018_09_06_125909_create_videos_table
Migrated:  2018_09_06_125909_create_videos_table
Migrating: 2018_09_12_064922_create_labels_table
Migrated:  2018_09_12_064922_create_labels_table
Migrating: 2018_09_14_073215_create_statuses_table
Migrated:  2018_09_14_073215_create_statuses_table
Migrating: 2018_09_14_114329_create_video_taggings_table
Migrated:  2018_09_14_114329_create_video_taggings_table
Migrating: 2018_09_15_105623_create_priorities_table
Migrated:  2018_09_15_105623_create_priorities_table
Migrating: 2018_09_17_044820_create_comments_table
Migrated:  2018_09_17_044820_create_comments_table
Migrating: 2018_09_24_130041_create_video_tagging_q_as_table
Migrated:  2018_09_24_130041_create_video_tagging_q_as_table

与@apokryfos 讨论后更新

现在迁移文件是

2014_10_12_000000_create_users_table
2014_10_12_100000_create_password_resets_table
2018_08_29_112331_entrust_setup_tables
2018_08_31_103313_create_audits_table
2018_09_06_064922_create_labels_table
2018_09_06_073215_create_statuses_table
2018_09_06_105623_create_priorities_table
2018_09_06_125909_create_videos_table
2018_09_14_114329_create_video_taggings_table
2018_09_17_044820_create_comments_table
2018_09_24_130041_create_video_tagging_q_as_table

我收到错误

Dropped all tables successfully.
Migration table created successfully.

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `videos` add constraint `videos_video_identified_by_foreign` foreign key (`video_identified_by`) references `users` (`id`))

  at D:\work\www\myapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      D:\work\www\myapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

  2   PDOStatement::execute()
      D:\work\www\myapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

因此，问题不在于创建迁移文件的日期，它似乎有所不同。

请使用参数 -v 查看更多详细信息。

标签： phplaravel-5.6laravel-migrations

解决方案

我解决了一些方法，但我想承认，每个人都应该非常严格地使用 Laravel 迁移。

错误是什么？

  • 我在本地工作，所以在开发过程中，您需要对表结构进行很多更改，并且在此过程中我还需要大量时间重新生成表，所以我的想法是，我不需要创建另一个迁移文件每次修改，我现在都在重新生成所有内容，当应用程序完成并且我需要进行更改时，我将使用单独的迁移文件。

问题是什么 ？

  • Laravel 正在根据迁移文件创建日期创建表，它永远不会使用您的文件更新：P，因此当创建一个表并且需要来自另一个表的外键（现在尚未创建）时，它将产生此错误。

因此，如果您的 FK 指向 PK，请确保源列存在。

你怎么能解决它？

  • 调整迁移文件生成的顺序。以正确的顺序生成新文件，复制内容，然后删除混乱的旧文件。

这从来都不是一个好主意，所以请严格遵守规则。

推荐阅读