Foreign keys with migrations: don’t forget UNSIGNED

While working on migration files in Laravel, it’s relatively easy to forget one small detail which will restrict you from creating foreign keys. Let me tell you more.

So for example, you have this piece of migrations code.

$table->integer('parental_document_id')->nullable();
$table->foreign('parental_document_id')
  ->references('id')
  ->on('registrants_documents');

Seems like everything’s ok – integer field and then foreign key on it. But after running artisan migrate, you will get this:

General error: 1215 Cannot add foreign key constraint 

1021_laraveldaily_unsigned

An error. “Cannot add foreign key constraint”. No details, no reason why, nothing. Just CANNOT. At first I thought that foreign key name was too long (limit is 64 symbols, by the way), but that wasn’t the case. The problem is that I forgot a small piece of the puzzle called unsigned():

$table->integer('parental_document_id')->unsigned()->nullable();

And then migration worked perfectly.

The thing is the ID field of parent table was built with $table->increments(‘id’);, which is, by default UNSIGNED. So, basically, you cannot build foreign key from signed field to a table with an unsigned ID field.

Of course, it’s a small detail, and probably most of you know that, but the problem here is that neither MySQL, nor Artisan specified the exact reason for an error – nothing says anything about unsigned field. So it might be useful if you ever encounter something like that.

Like our articles?
Check out our Laravel online courses!

6 COMMENTS

  1. When an error like this occurs, run “SHOW ENGINE INNODB STATUS” in a SQL query and inspect the results. You will get a LAST FOREIGN KEY ERROR heading with much more information.

    In this case, it would display that you cannot use foreign keys on different sized columns (10 != 11)

  2. One other thing that is good to look for is… if you are adding/altering tables in an existing DB check to make sure the table you are linking is using MYISAM, I had this issue once before and I had a hard time figuring it out but thanks to a similar answer as Francis’s, I was able to determine the cause and find a fix… Great articles by the way! Short and informative.

LEAVE A REPLY

Please enter your comment!
Please enter your name here