Laravel 5.5中errno:150「外键约束格式错误」问题求助

Hey there, let's work through that foreign key error you're facing. This error almost always boils down to a mismatch between your foreign key setup and the requirements of the referenced table. Let's break down the most likely issues and how to fix them using your table schemas as a reference.

1. Match Column Types Exactly #

The users.id column is defined as an unsigned incrementing integer ($table->increments('id')->unsigned();). For a foreign key to this column to work, the corresponding column in users_references must have the exact same data type—that means it needs to be an unsigned integer too.

If your incomplete users_references schema uses a regular integer() without the unsigned() modifier, that's almost certainly the problem. Here's how your users_references should look to properly link to users:

Schema::create('users_references', function (Blueprint $table) {
    $table->increments('id');
    // These columns must match users.id's type: unsigned integer
    $table->unsignedInteger('teacher_id');
    $table->unsignedInteger('student_id');
    
    // Add foreign key constraints
    $table->foreign('teacher_id')
          ->references('id')
          ->on('users')
          ->onDelete('cascade'); // Optional: adjust based on your data retention needs
    
    $table->foreign('student_id')
          ->references('id')
          ->on('users')
          ->onDelete('cascade');
});

2. Ensure Both Tables Use the InnoDB Engine #

Foreign key constraints only work with the InnoDB storage engine (MyISAM doesn't support them). While modern MySQL defaults to InnoDB, it's worth confirming:

• You can explicitly set the engine in each schema:

  Schema::create('users', function (Blueprint $table) {
      $table->engine = 'InnoDB';
      // Rest of your users table schema...
  });
  

• Or set the default engine globally in config/database.php under the MySQL connection settings.

3. Check Migration Execution Order #

You need to create the users table before users_references, since the latter depends on the former. Laravel runs migrations in order of their filename timestamps—make sure your users migration has an earlier timestamp than users_references. If not, rename the files to adjust the order.

4. Verify No Mismatched Existing Data #

If you're adding this foreign key to an already populated users_references table, make sure every value in your foreign key columns (e.g., teacher_id, student_id) exists in users.id. Any orphaned records will cause the constraint creation to fail.

Double-check these points, especially the column type matching—it's the most common culprit here. Let me know if you still run into issues after trying these fixes!

内容的提问来源于stack exchange，提问作者akhil