“一般错误:1005 无法创建表"使用 Laravel Schema 构建和外键

2022-01-20 00:00:00 foreign-keys mysql laravel

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

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

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

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

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');
});

问题是,当我运行此代码时(在/setup 路由中),我收到以下错误:

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

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 (
)

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

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

users 已经存在,并且它确实有一个 id 字段,即 auto_increment.我还在用正确的关系(belongs_tohas_many)设置我的模型,但据我所知,这不是问题——这是数据库设置.DB 是 InnoDB.

users already exists and it does have an id field that is auto_increment. I am also setting up my models with the proper relationships (belongs_to and has_many), but as far as I can tell this is not the issue-- it's the database setup. The DB is InnoDB.

外键到底做错了什么?

推荐答案

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

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.

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.

相关文章