Level 1
try to replace the
$table->id();
to this
$table->bigIncrements('id');
Oct 17, 2021
14
Level 1
New Laravel 8 project - default users migration throws "Cannot add foreign key constraint"
I just created a new Laravel 8 project and running the migrations created during project creation. 3 of the four migrations run fine with no errors and the tables are created correctly. The CreateUsersTable migration crashes with the error:
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: create table `users` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(255) not null, `email` varchar(255) not null, `email_verified_at` timestamp null, `password` varchar(255) not null, `remember_token` varchar(100) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')
The weird thing about this is, there are NO foreign keys involved.
Here's the migration - it's exactly as it came "out of the box":
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
The only thing I've modified since I created the new project is the .env file to get the project to connect to the database. Everything else is just as generated by Laravel Installer. Just prior to creating the new project, I reinstalled the Laravel Installer to ensure I have the latest version. No additional frameworks or packages at this point.
I've created numerous Laravel projects before and never run into any problems getting the default migrations to run in my development environment, but this is the first one I've done with Laravel 8.
Thanks for any help anyone can offer.
Level 73
@jufrensius The first one is syntactic sugar of the second one so they do the same thing.
Level 1
I'm presuming I shouldn't have to debug the code that comes out of the box with Laravel, right?
But just to see what I could find out, I tried commenting out some of the table fields, and it just keeps coming back with that error no matter what I do to it.
I even tried creating the project with Laravel 7, and still get the same thing.
Level 18
@s2d-kate Well if you can't event migrate with laravel7 witch is what you used previously, that must mean something changed outside of your laravel installation.
Level 73
@s2d-kate What OS are you running?
Level 1
@STEREOH , exactly. My #1 suspect at this point is something in the database configuration, but since the error message is obviously not really about what it says it is, it's a little difficult to track it down based on that.
I do have two other projects in my test environment, one of which is running on Laravel 7, and it hasn't had any database issues. I haven't run any migrations with it recently, though, and this issue may be specific with migrations.
Level 1
@Tray2 Ubuntu 18. (I know, it's a bit out of date. I'm waiting for delivery on a newer computer so I can upgrade to 21.) It's running MySQL 5.7.xx.
Level 73
Level 1
@Tray2 - thanks. I'll look these over and see if I've overlooked something.
Level 1
I found the exact cause of this mysterious foreign key violation. It came from a table in another database I was messing around with earlier, which had a foreign key pointing to a users table in my app database that I later dropped. I thought I dropped the tables from the other database as well, then totally forgot about it. But it was still lurking out there, outside of my tiny little sphere of attention.
Here's how I was able to track it down:
The message I included in my original post only said there was a foreign key violation, but didn't provide any specifics about exactly what foreign key it was referring to.
After more google searches, I found a way - thanks to Christof Mewes at xrstf.de - to get MySQL to give up its [expletive] secrets when it reports a foreign key violation. Immediately after the error occurs, in your mysql client, type the command:
mysql> SHOW ENGINE INNODB STATUS;
In my case, I got this:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-10-19 21:20:53 0x7f3edc140700 Error in foreign key constraint of table mydb/billing:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT `fk_billing_users1` FOREIGN KEY (`users_id`) REFERENCES `appname_db`.`users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
The index in the foreign key in table is fk_billing_users1_idx
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
No wonder I couldn't find it. I was looking in my app database for foreign keys pointing to the users table, and it ended up being in some other database.
So, stupid mistake on my part, but I decided to share this in case anyone else benefit from this tip on debugging foreign key errors.
1 like
Level 73
@s2d-kate So what you are saying is that you used the same database before and didn't drop all the objects in it before you started a new laravel project with it? So one foreign key constraint was left in the database?
Level 1
@Tray2 Not exactly.
I was testing some ideas between my app database and another database before I started the actual project. I did drop all the objects from my app database before starting the project, but forgot to drop the tables in the other database that had foreign keys pointing to one of the tables in my app database.
Level 73
@s2d-kate They should never interfere with each other. What you do in one database is scoped for just that database and should not affect any other database in the same RDBMS. So I find this very curious indeed.
Level 1
@Tray2 I didn't think so, either, but they did.
You can see from the error message MySQL was objecting to the creation of a 'users' table in the 'appname_db' database because it didn't contain a field that matched the properties of a foreign key that was set in the 'billing' table in the 'mydb' database. As soon as I dropped the tables in the 'mydb' database, the migration to create the 'users' table in 'appname_db' ran fine. ('appname_db' is just an alias for the database's real name.)
I'll have to try replicating it somewhere else where it won't affect my project and provide some more evidence. But that's really not much of a priority, I'd like to move on with my project now that I've solved my problem.
Please or to participate in this conversation.