php laravel errno 150 外键约束的格式不正确

laravel errno 150 foreign key constraint is incorrectly formed

提问by Yrtymd

Can anybody help me to solve this problem?

有人可以帮我解决这个问题吗？

There are 3 tables with 2 foreign keys:

有 3 个表，有 2 个外键：

         Schema::create('users', function (Blueprint $table) {
                    $table->increments('id');
                    $table->string('name');
                    $table->string('email')->unique();
                    $table->string('password');
                    $table->rememberToken();
                    $table->timestamps();
                });

        Schema::create('firms', function (Blueprint $table) {
                    $table->increments('id');
                    $table->string('title')->nullable();  
                    $table->integer('user_id')->unsigned()->nullable();
                    $table->foreign('user_id')->references('id')->on('users');
                    $table->timestamps();
                });
       Schema::create('jobs', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title')->nullable();
            $table->integer('firm_id')->unsigned()->nullable();
            $table->foreign('firm_id')->references('id')->on('firms');
            $table->timestamps();
        });

Error after running migration:

运行迁移后出错：

[Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `job`.`#sql-5fc_a1`
   (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter ta
  ble `firms` add constraint `firms_user_id_foreign` foreign key (`user_id`)
  references `users` (`id`))

  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `job`.`#sql-5fc_a1`
   (errno: 150 "Foreign key constraint is incorrectly formed")

回答by Shadow

In case of foreign keys, the referenced and referencing fields must have exactly the same data type.

对于外键，引用字段和引用字段必须具有完全相同的数据类型。

You create the idfields in both usersand firmsas signedintegers. However, you create both foreign keys as unsignedintegers, therefore the creation of the keys fail.

您以有符号整数和有符号整数形式创建id字段。但是，您将两个外键都创建为无符号整数，因此键的创建失败。usersfirms

You need to either add the unsignedclause to the idfield definitions, or remove the unsignedclause from the foreign key fields.

您需要将该unsigned子句添加到id字段定义中，或者unsigned从外键字段中删除该子句。

回答by Bobby Axe

This answer is not better than the six answers before it but it is a more comprehensive answer on what causes laravel-errno-150-foreign-key-constraint-is-incorrectly-formedand how to fix specifically for laravel.

这个答案并不比之前的六个答案好，但它是关于什么原因laravel-errno-150-foreign-key-constraint-is-incorrectly-formed以及如何专门针对 laravel 修复的更全面的答案。

1) Spelling: often at times a wrong spelling of the referenced column nameor referenced table namecan throw up this error and you won't know as the error trace is not very descriptive.

1)拼写：有时，引用column name或引用的错误拼写table name可能会引发此错误，您不会知道，因为错误跟踪不是很具有描述性。

2) Unique: the referenced column must be unique or indexed either by adding ->primary()or adding ->unique()or adding ->index()to the column definition in your migration.

2)唯一：通过在迁移中添加->primary()或添加->unique()或添加->index()到列定义，引用的列必须是唯一的或索引。

3) Data type: the referenced and referencing fields must have exactly the same data type. this can not be stressed enough.

3)数据类型：引用字段和引用字段必须具有完全相同的数据类型。这一点再怎么强调也不为过。

• for bigincrementsexpected data type is bigInteger('column_name')->unsigned();

• for incrementsexpected is integer('column_name')->unsigned();etc.

• 对于bigincrements预期的数据类型是bigInteger('column_name')->unsigned();

• 因为increments预期是integer('column_name')->unsigned();等。

4) Remnants: when this error occurs it does not mean that the table is not migrated rather it is migrated but the foreign key columns are not set and it is not added to the migration tablehence running php artisan migrate:resetwill remove other tables except the faulty tables, so a manual drop of the faulty table is recommended to avoid further errors.

4) Remnants: 当这个错误发生时，这并不意味着表没有被迁移，而是被迁移但没有设置外键列并且没有添加到migration table因此运行php artisan migrate:reset将删除除故障表之外的其他表，因此建议手动删除故障表以避免进一步的错误。

5) Order: this is often the most usual cause of this error the table being referencedmust be created or migrated before the reference tableelse artisan wont find where to integrate the foreign key. to ensure an order for the migration process rename the migration file example:

5）顺序：这通常是此错误的最常见原因，referenced必须在reference table其他工匠找不到集成外键的位置之前创建或迁移表。为确保迁移过程的顺序重命名迁移文件示例：

• Table A:2014_10_12_000000_create_users_table.phpand
• Table B:2014_10_12_100000_create_password_resets_table.php

• 表 A：2014_10_12_000000_create_users_table.php和
• 表 B：2014_10_12_100000_create_password_resets_table.php

This indicates that Table A will always come before Table B to change that, i will rename Table B to 2014_10_11_100000_create_password_resets_table.phpnow it will migrate before Table A.

这表明表 A 将始终在表 B 之前进行更改，我将表 B 重命名为2014_10_11_100000_create_password_resets_table.php现在它将在表 A 之前迁移。

6) Enable Foreign Key: if all else fails then add Schema::enableForeignKeyConstraints();inside your function up()before your migration code example:

6）启用外键：如果一切都失败了，然后添加Schema::enableForeignKeyConstraints();你的内部function up()迁移的代码示例之前：

class CreateUsersTable extends Migration
{
 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
    Schema::enableForeignKeyConstraints();
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
 }

 /**
  * Reverse the migrations.
  *
  * @return void
  */
 public function down()
 {
    Schema::dropIfExists('users');
 }
}

To read more see laravel foreign keyand laravel migrations

要阅读更多信息，请参阅laravel 外键和laravel 迁移

Mention any more fixes that i missed in the comments thanks.

在评论中提及我错过的更多修复，谢谢。

回答by Mayank Dudakiya

Most of the time this kind of error occurs due to the datatypemismatch on both the table.

大多数情况下，这种错误的发生是由于两个表上的数据类型不匹配。

Both primary key table & foreign key table should use same datatype and same option.

主键表和外键表都应该使用相同的数据类型和相同的选项。

For example:

例如：

users

用户

        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

orders

订单

 Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products');
            $table->bigInteger('user_id')->unsigned();
           $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

            $table->timestamp('added_on');
        });

On above example, I am trying to assign foreign key to users table from order table but I have bigIntegerdatatable in order table while in user table, I have simple integer. That's why it's generated this kind of error.

在上面的例子中，我试图从订单表中为用户表分配外键，但我在订单表中有bigInteger 数据表，而在用户表中，我有简单的整数。这就是它产生这种错误的原因。

Also, If you have used unsigned(), nullable()etc options with primary or foreign key then you should use same at both the place.

此外，如果您使用了unsigned()、nullable()等带有主键或外键的选项，那么您应该在这两个位置使用相同的选项。

回答by Tino Tadez

For PHP laravel 5.8 use unsigned modifier in this format

对于 PHP laravel 5.8 使用这种格式的无符号修饰符

$table->unsignedBigInteger('user_id');

drop all tables in the database and run the migration again

删除数据库中的所有表并再次运行迁移

回答by Netwons

public function up() {     Schema::create('companies', function (Blueprint $table) {         $table->bigIncrements('id');         $table->string('name');         $table->text('address');         $table->string('tel1');         $table->string('tel2');         $table->integer('owner');         $table->unsignedBigInteger('access_id');         $table->string('depot_number')->default(2);         $table->timestamps();           $table->foreign('access_id')->references('id')->on('accesses')             ->onDelete('cascade');     }); } 

public function up() {     Schema::create('accesses', function (Blueprint $table) {         $table->bigIncrements('id');         $table->string('type');         $table->string('description');         $table->timestamps();     }); } 

In your database/migrationsfolder, sort by name. Then make sure create_accesses_tableis before create_companies_tablehere:

在您的数据库/迁移文件夹中，按名称排序。然后确保create_accesses_table在create_companies_table之前：

回答by Saad Tejda

we are table villes:

我们是桌村：

Schema::create('villes', function (Blueprint $table) {
            $table->bigIncrements('idVille'); // bigIncrement(8 bits)
            $table->string('nomVille');
            $table->timestamps();
        });

foreign key in table users for exemple :

表用户中的外键例如：

Schema::table('users', function (Blueprint $table) {
            $table->bigInteger('ville_idVille')->unsigned()->after('tele');
            $table->foreign('ville_idVille')->references('idVille')->on('villes');
        });

回答by Louie Guides

• users
• cashier refers users
• student refers cashier

• 用户
• 收银员转介用户
• 学生转介收银员

In addition when declaring foreign keys in laravel all tables your are referring must be on the top. In this case you can use "->unsigned()" modifier..

此外，在 laravel 中声明外键时，您引用的所有表都必须位于顶部。在这种情况下，您可以使用“->unsigned()”修饰符..

回答by Uriah Galang

This happens when you test your laravel app and you have used an instance of MySQL in your tests that is subject to database migrations.

当您测试 Laravel 应用程序并且您在测试中使用了受数据库迁移影响的 MySQL 实例时，就会发生这种情况。

Prior to running the test, my migrations work fine. Then I encountered this error.

在运行测试之前，我的迁移工作正常。然后我遇到了这个错误。

The way I resolved this issue was to simply drop the whole database and re-create it. Then migrate again.

我解决这个问题的方法是简单地删除整个数据库并重新创建它。然后再次迁移。

回答by Ehab Elzeny

if you set referencing fields and be have exactly the same data type but error exists you can change date migration file just its working

如果您设置了引用字段并且具有完全相同的数据类型但存在错误，您可以更改日期迁移文件的工作

回答by Mz1907

I tried all the answers provided in this thread.

我尝试了此线程中提供的所有答案。

Nothing worked.

没有任何效果。

Then I discovered I forgot to remove the "migrations" table in phpMyadmin.

然后我发现我忘记删除 phpMyadmin 中的“迁移”表。

I removed all tables from phpMyAdmin but the migrations table. That's why the error persisted again and again.

我从 phpMyAdmin 中删除了所有表，但迁移表。这就是为什么错误一次又一次地持续存在的原因。