Laravel迁移自引用外键问题

Hi I have a problem to create a table using migration schema builder. The problem occure with table with self referencing foreign key. Here is the code which produce error:

您好我有一个问题,使用迁移架构生成器创建一个表。使用自引用外键的表发生此问题。这是产生错误的代码:

        Schema::create('cb_category', function($table)
    {
        $table->integer('id')->primary()->unique()->unsigned();
        $table->integer('domain_id')->unsigned();
        $table->foreign('domain_id')->references('id')->on('cb_domain'); 
        $table->integer('parent_id')->nullable(); 
        $table->foreign('parent_id')->references('id')->on('cb_category')->onUpdate('cascade')->onDelete('cascade'); 
        $table->string('name');
        $table->integer('level');
    });

Here is the error:

这是错误:

  SQLSTATE[HY000]: General error: 1005 Can't create table 'eklik2.#sql-7d4_e' (errno: 150) (SQL: alter table `cb_cate

goryadd constraint cb_category_parent_id_foreign foreign key (parent_id) referencescb_category(id`) on del ete cascade on update cascade) (Bindings: array ( ))

goryadd约束cb_category_parent_id_foreign外键(parent_id)referencescb_category(id`)on del ete cascade on update cascade)(Bindings:array())

[PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table 'eklik2.#sql-7d4_e' (errno: 150)

[PDOException] SQLSTATE [HY000]:常规错误:1005无法创建表'eklik2。#sql-7d4_e'(错误号:150)

Any idea?

3 个解决方案

#1

You have to break this into two Schema blocks, one creating the columns, the other adding the FKs. mysql can't do both at the same time.

你必须将它分成两个Schema块,一个创建列,另一个添加FK。 mysql不能同时做到这两点。

#2

I may be too late for the party, but the official docs claim that the foreign key, in case of integer, must be ->unsigned();

对于派对来说,我可能为时已晚,但官方文档声称外键,如果是整数,则必须是 - > unsigned();

http://laravel.com/docs/4.2/schema#foreign-keys

Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.

注意:创建引用递增整数的外键时,请记住始终使外键列无符号。

Also, Artisan does not fail if you (as I have) misspell unsigned() and I have spent quite a few hours trying to figure out why the key was not created.

此外,如果你(因为我)拼错unsigned()并且我花了几个小时试图弄清楚为什么没有创建密钥,Artisan也不会失败。

So two things: 1. Always make the foreign key column unsigned in case of incrementing integers 2. Check the spelling of unsigned()

所以有两件事:1。在增加整数的情况下,始终使外键列无符号2.检查unsigned()的拼写

#3

Schema::create('cb_category', function (Blueprint $table) {
        $table->increments('id')->unsigned();
        $table->integer('domain_id')->unsigned();
        $table->foreign('domain_id')->references('id')->on('cb_domain');
        $table->integer('parent_id')->nullable();
        $table->foreign('parent_id')->references('id')->on('cb_category')->onUpdate('cascade')->onDelete('cascade');
        $table->string('name');
        $table->integer('level');
    });

Try this

#1