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.

No comments or questions yet...

Like our articles?

Become a Premium Member for $129/year or $29/month

Written by

You might also like