Foreign Key Constraint Error in Laravel

php

Ever find yourself facing this kind of error when adding foreign key constraints in Laravel?

1
2
3
Illuminate\Database\QueryException

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `XXX` add constraint `name-of-constraint` foreign key (`fk`) references `YYY` (`id`))

Not too helpful, right? If only it would say what was wrong, exactly…

Most likely, your problem is caused by incorrect data types on the key fields (primary and foreign).

Firstly, consider using integers rather than big integers. That done, make sure that the types all match and that you are using an unsigned integer for your foreign key.

This is best illustrated with an example. Let’s imagine that I have a table named XXX with a related table YYY. YYY has a foreign key named fk which references the id field of XXX.

For now:

  • Ignore the names (that’s just to make it easier to read). I am definitely not advocating naming foreign keys fk for instance!
  • I’m also specifically naming the id field for clarity, which isn’t necessary in Laravel, since it’s a convention.

My migrations (spread over two files) look like this:

1
2
3
4
5
6
7
8
9
10
11
Schema::create('XXX', function (Blueprint $table) {
    $table->increments('id');
    ...
});

Schema::create('YYY', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('fk')->unsigned();
    $table->foreign('fk')->references('id')->on('Stat');
    ...
});

The key points to take from this are that both id fields use increments, which is an unsigned integer. There are performance reasons for this and I am certain I won’t be adding or creating 2+ billion records.

In addition, when specifying the fk column, I mark it as unsigned to match the types of the id fields.

Fingers crossed that fixes any issues you have with this error.

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com