SQL 1005 error when running Laravel 4 migration
laravel-4, mysql, php, sql
Solution
`$table->increments('id')` produces an unsigned integer. Integers are not compatible with unsigned integers when setting up a foreign key.
Try this:
$table->unsignedInteger('user_id')->nullable();
$table->foreign('user_id')->references('id')->on('users');
From laravel docs (http://laravel.com/docs/schema#foreign-keys):
Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.
Problem
I'm trying to set up a `comments` table with a foreign key to the `users` table with Laravel 4's schema builder, like so: ``` Schema::create('comments', function(Blueprint $table) { $table->increments('id'); $table->integer('user_id'); $table->text('body'); $table->timestamps(); $table->foreign('user_id')->references('id')->on('users'); }); ``` But when I run the migration, I get the following error message: ``` [Exception] SQLSTATE[HY000]: General error: 1005 Can't create table 'streamr.#sql-16fc_89' (errno: 150) (SQL: alter table `comments` add constraint comments_user_id_foreign foreign key (`user_id`) references `users` (`id`)) (Bindings: array ()) ``` As far as I can tell, this is because the `id` column of the `users` table is `int(10)`, and `$table->integer('user_id')` makes an `int(11)` column causing the foreign key to fail due to incompatible column types. However, when I try to set the length of the integer column I'm creating it doesn't work: ``` $table->integer('user_id', 10); ``` Is there any way to get around this? Seems strange to me that Laravel's schema builder will build a `int(10)` column for primary keys and not make them compatible with `integer` columns :/ Edit: I've also made sure the tables are InnoDB, and they are.