MySQL “一般错误：1005 无法创建表”使用 Laravel 架构构建和外键

"General error: 1005 Can't create table" Using Laravel Schema Build and Foreign Keys

提问by Andrew M

Essentially, I am having the same issue as this guy, minus the table prefix. Because I have no table prefix, his fix does not work. http://forums.laravel.com/viewtopic.php?id=972

基本上，我和这个人有同样的问题，减去表前缀。因为我没有表前缀，他的修复不起作用。http://forums.laravel.com/viewtopic.php?id=972

I am trying to build a table using Laravel's Schema Builder like this:

我正在尝试使用 Laravel 的 Schema Builder 构建一个表，如下所示：

Schema::create('lessons', function($table)
{
    $table->increments('id');
    $table->string('title')->nullable();
    $table->string('summary')->nullable();
    $table->timestamps();
});

Schema::create('tutorials', function($table)
{
    $table->increments('id');
    $table->integer('author');
    $table->integer('lesson');
    $table->string('title')->nullable();
    $table->string('summary')->nullable();
    $table->string('tagline')->nullable();
    $table->text('content')->nullable();
    $table->text('attachments')->nullable();
    $table->timestamps();
});

Schema::table('tutorials', function($table)
{
    $table->foreign('author')->references('id')->on('users');
    $table->foreign('lesson')->references('id')->on('lessons');
});

The issue is, when I run this code (in a /setup route), I get the following error:

问题是，当我运行此代码（在 /setup 路由中）时，出现以下错误：

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

SQL: ALTER TABLE `tutorials` ADD CONSTRAINT tutorials_author_foreign FOREIGN KEY (`author`) REFERENCES `users` (`id`)

Bindings: array (
)

Based on posts around the web and the limited documentation available on how to setupLaravel's Eloquent relationships, I'm not sure what I'm doing wrong...

基于网络上的帖子和关于如何设置Laravel 的 Eloquent 关系的有限文档，我不确定我做错了什么......

usersalready exists and it doeshave an idfield that is auto_increment. I am also setting up my models with the proper relationships (belongs_toand has_many), but as far as I can tell this is not the issue-- it's the database setup. The DB isInnoDB.

users已经存在并且它确实有一个id字段是auto_increment. 我也在用正确的关系（belongs_to和has_many）设置我的模型，但据我所知，这不是问题——这是数据库设置。数据库是InnoDB。

What exactly am I doing wrong with the foreign key?

我对外键到底做错了什么？

采纳答案by David Barker

I'm not 100% sure if these are the reasons this is failing but a couple of pointers. If you're using an older version of mySQL as the database, the default table implementation is myISAM that does not support foreign key restraints. As your scripts are failing on the foreign key assignment, you are better off explicitly stating that you want INNODB as the engine using this syntax in Schema's create method.

我不是 100% 确定这些是否是失败的原因，但有几个指针。如果您使用旧版本的 mySQL 作为数据库，默认的表实现是不支持外键限制的 myISAM。由于您的脚本在外键分配上失败，您最好在 Schema 的 create 方法中使用此语法明确声明您希望 INNODB 作为引擎。

Schema::create('lessons', function($table)
{
    $table->engine = 'InnoDB';

    $table->increments('id');
    $table->string('title')->nullable();
    $table->string('summary')->nullable();
    $table->timestamps();
});

This should hopefully alleviate the problems you are having.

这应该有望缓解您遇到的问题。

Also, whilst you can declare foreign keys as an afterthought, I create the foreign keys within the initial schema as I can do an easy check to make sure I've got the right DB engine set.

此外，虽然您可以事后声明外键，但我会在初始模式中创建外键，因为我可以轻松检查以确保我拥有正确的数据库引擎集。

Schema::create('tutorials', function($table)
{
    $table->engine = 'InnoDB';

    $table->increments('id');
    $table->integer('author');
    $table->integer('lesson');
    $table->string('title')->nullable();
    $table->string('summary')->nullable();
    $table->string('tagline')->nullable();
    $table->text('content')->nullable();
    $table->text('attachments')->nullable();
    $table->timestamps();

    $table->foreign('author')->references('id')->on('users');
    $table->foreign('lesson')->references('id')->on('lessons');
});

Hope this helps / solves your problem.

希望这有助于/解决您的问题。

回答by maackle

I've been having the same problem. I just noticed the following note at the very bottom of the Laravel Schema docs:

我一直有同样的问题。我刚刚在Laravel Schema 文档的最底部注意到以下注释：

Note: The field referenced in the foreign key is very likely an auto increment and therefore automatically an unsigned integer. Please make sure to create the foreign key field with unsigned() as both fields have to be the exact same type, the engine on both tables has to be set to InnoDB, and the referenced table must be created before the table with the foreign key.

注意：外键中引用的字段很可能是自动递增的，因此自动是无符号整数。请确保使用 unsigned() 创建外键字段，因为这两个字段必须是完全相同的类型，两个表上的引擎都必须设置为 InnoDB，并且必须在具有外键的表之前创建被引用的表.

For me, as soon as I set my foreign key fields as such:

对我来说，一旦我将外键字段设置为：

$table->integer('author')->unsigned();

I had no problem.

我没有问题。

EDIT: Also, make sure that the fields in the foreign table are already created, otherwise this may fail with the same error.

编辑：另外，确保外部表中的字段已经创建，否则这可能会失败并出现相同的错误。

回答by Tim Ogilvy

A Summary of the answers already listed, plus mine:

已经列出的答案摘要，加上我的：

1. Foreign Keys generally require InnoDb, so set your default engine, or explicitly specify

   $table->engine = 'InnoDB';
   

2. Foreign keys require the referenced table to exist. Make sure the referenced table is created in an earlier migration, prior to creating the key. Consider creating the keys in a separate migration to be sure.

3. Foreign Keys require the data type to be congruent. Check whether the referenced field is the same type, whether its signed or unsigned, whether it's length is the same (or less).

4. If you are switching between hand-coding migrations, and using generators, make sure you check the id type you are using. Artisan uses increments()by default but Jeffrey Way appears to prefer integer('id', true).

1. 外键通常需要InnoDb，所以设置你的默认引擎，或者明确指定

   $table->engine = 'InnoDB';
   

2. 外键要求引用的表存在。在创建键之前，确保引用的表是在较早的迁移中创建的。考虑在单独的迁移中创建密钥以确保。

3. 外键要求数据类型一致。检查引用的字段是否相同类型，是否有符号或无符号，长度是否相同（或更少）。

4. 如果您在手动编码迁移和使用生成器之间切换，请确保检查您使用的 id 类型。Artisan默认使用increments()，但 Jeffrey Way 似乎更喜欢integer('id', true)。

回答by Justin Flammia

I ran into this issue too.

我也遇到了这个问题。

The solution I found is that the tables that contain the id that is being used a foreign id needs to be created before another table can reference it. Basically, you are creating a table and telling MySQL to reference another table's primary key but that table doesn't exist yet.

我发现的解决方案是，在另一个表可以引用它之前，需要创建包含正在使用的外部 ID 的表。基本上，您正在创建一个表并告诉 MySQL 引用另一个表的主键，但该表尚不存在。

In your example, the author and lesson tables need to be created first.

在您的示例中，需要先创建作者表和课程表。

The order in which the tables are created is dependent on artisan and the order you created your migration files.

创建表的顺序取决于 artisan 和您创建迁移文件的顺序。

My opinion would be to empty out your database of all the tables and change the timestamps in the migration file names (or delete them and recreate them in the correct order) so that your author and lesson tables are created before your tutorials table.

我的意见是清空所有表的数据库并更改迁移文件名中的时间戳（或删除它们并以正确的顺序重新创建它们），以便在教程表之前创建作者和课程表。

回答by Alexander Taubenkorb

I received the same error because I forgot to set the table type to InnoDB on the referenced table:

我收到了同样的错误，因为我忘记在引用的表上将表类型设置为 InnoDB：

$table->engine = 'InnoDB';

回答by Nima Ghaedsharafi

Laravel5, MySQL55, CentOS65

Laravel5、MySQL55、CentOS65

in my case the errors were same as this.

在我的情况下，错误与此相同。

Error: [Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table 'nabsh.#sql-5b0_e' (errno: 121) (SQL: alter table usermetaadd constraint usermeta_uid_foreign foreign key (uid) ref erences users(id) on delete cascade).

错误：[Illuminate\Database\QueryException]
SQLSTATE[HY000]：一般错误：1005 Can't create table 'nabsh.#sql-5b0_e' (errno: 121) (SQL: alter table usermetaadd constraint usermeta_uid_foreign Foreign key ( uid) 引用 users( id) 在删除级联上）。

I found a good tip in this problem's approved answer: ERROR: Error 1005: Can't create table (errno: 121)

我在这个问题的批准答案中找到了一个很好的提示： 错误：错误 1005：无法创建表（错误号：121）

Tip: You will get this message if you're trying to add a constraint with a name that's already used somewhere else.

提示：如果您尝试使用已在其他地方使用的名称添加约束，您将收到此消息。

in my case this error means the constraint which has been tried to add is already exists somewhere (but i couldn't see them anywhere).

在我的情况下，此错误意味着已尝试添加的约束已存在于某处（但我在任何地方都看不到它们）。

I copied the output error query and execute it within sequel pro, then hopefully I saw the same error again. as the tip says I changed the constraint name to something else then it affected with no error so there's a problem with constraint name builder in laravel5 + MySQL55 + CentOS65.

我复制了输出错误查询并在 sequel pro 中执行它，然后希望我再次看到相同的错误。正如提示所说，我将约束名称更改为其他名称，然后它没有错误地受到影响，因此 laravel5 + MySQL55 + CentOS65 中的约束名称构建器存在问题。

Solution:try to rename constraints by sending the second parameter at foreign method in table schema.

解决方案：尝试通过在表模式的外部方法中发送第二个参数来重命名约束。

Schema::table('tutorials', function($table)
{
    $table->foreign('author')->references('id')->on('users');
    $table->foreign('lesson')->references('id')->on('lessons');
});

to ->

到->

Schema::table('tutorials', function($table)
{
    $table->foreign('author', 'fk_tutorials_author')->references('id')->on('users');
    $table->foreign('lesson', 'fk_tutorials_lesson')->references('id')->on('lessons');
});

i hope it helps. it works in my case

我希望它有帮助。它适用于我的情况

回答by joffdd

This happened to me in Yii Framework 1.x migrations too. Turned out that similarily to the Laravel solutions above it may be caused by a

这在 Yii Framework 1.x 迁移中也发生在我身上。原来，类似于上面的 Laravel 解决方案，它可能是由

• wrong spelled (or not yet existing) table name
• wrong spelled (or not yet existing) column name
• duplicate foreign key name in the same database being used in different tables

• 拼写错误（或尚不存在）的表名
• 拼写错误（或尚不存在）的列名
• 在不同表中使用的同一数据库中的重复外键名称

回答by Guka Gunia

when using foreign key-smake sure that your foreign key is unsigned. this worked for me

使用外键时，请确保您的外键是unsigned. 这对我有用

回答by Nina

You have to give the integer an unsigned flag in Laravel 5.4, like this:

你必须在 Laravel 5.4 中给整数一个无符号标志，像这样：

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->increments('post_id');
        $table->text('title');
        $table->text('text');
        $table->integer('employee_post_id_number')->unsigned();
        $table->foreign('employee_post_id_number')->references 
        ('employee_id_number')->on('employees');
        $table->timestamps();
    });
}

回答by Daniel

Easiest way is to disable foreign key checks:

最简单的方法是禁用外键检查：

DB::statement('set foreign_key_checks=0');

Schema::table( ... );

DB::statement('set foreign_key_checks=1');