Laravel 5.6中外键约束创建失败问题求助

Hey there! Let's work through why that foreign key error is popping up—this is a super common gotcha when setting up relationships in Laravel, so we'll get it sorted quickly.

First, let's recap the error you're seeing:

SQLSTATE[HY000]: General error: 1005 Can't create table inventar.#sql-fd4_141 (errno: 150 "Foreign key constraint is incorrectly formed")（执行的SQL语句：alter table items add constraint items_lend_foreign foreign key ( lend ) references lending ( id )）

Here are the most likely culprits and how to fix them:

1. Mismatched Data Types #

This is the #1 reason for this error. The foreign key field in your items table (lend) must exactly match the data type of the primary key it's referencing in the lending table (id).

• Laravel's default $table->id() creates an unsigned bigint (64-bit integer). So your lend field needs to be unsignedBigInteger too.
• If you accidentally used $table->integer('lend') instead of $table->unsignedBigInteger('lend'), that's the root of the problem.

2. Incorrect Migration Order #

You can't create a foreign key to a table that doesn't exist yet! Laravel runs migrations in the order of their filename timestamps.

• Make sure your lending table migration runs before the items table migration. Check the filenames in database/migrations—the timestamp at the start of the lending file should be earlier than the items file.
• If the order is wrong, either rename the files to adjust the timestamps, or run the lending migration first manually:

  php artisan migrate --path=database/migrations/[your-lending-migration-filename].php
  

  Then run the rest of the migrations.

3. Unsupported Storage Engine #

Foreign keys only work with the InnoDB storage engine. MyISAM (an older engine) doesn't support foreign key constraints.

• Laravel uses InnoDB by default, but double-check your migrations or config/database.php to make sure you haven't changed the default engine.
• You can explicitly set the engine in your migration if needed:

  Schema::create('lending', function (Blueprint $table) {
      $table->engine = 'InnoDB';
      $table->id();
      // ... other fields
  });
  

4. The Referenced Field Isn't a Primary/Unique Key #

Your lending.id should be the primary key (which it is if you used $table->id()), but if for some reason it's not, that will break the foreign key. Double-check that lending.id is defined as the primary key.

Recommended Fix: Use Laravel's Built-in Foreign Key Helpers #

To avoid manual mistakes, use Laravel's foreignId() and constrained() methods—they handle data types and references automatically:

Example lending Migration: #

Schema::create('lending', function (Blueprint $table) {
    $table->id();
    // Add your other fields here (e.g., borrower_name, lend_date)
    $table->timestamps();
});

Example items Migration: #

Schema::create('items', function (Blueprint $table) {
    $table->id();
    // This automatically creates an unsigned bigint field and links it to lending.id
    $table->foreignId('lend')->nullable()->constrained('lending');
    // Add your other item fields here (e.g., name, quantity, category)
    $table->timestamps();
});

The nullable() is optional—use it if an item might not be lent out. If every item must have a lend record, remove nullable().

Final Steps #

1. Rollback any existing failed migrations:

   php artisan migrate:rollback
   

2. Fix your migration files based on the above checks.
3. Run the migrations again:

   php artisan migrate
   

That should resolve the foreign key error! Let me know if you hit any other snags.

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