Level 104
Are the users and types tables created before articles?
Dec 16, 2019
14
Level 1
Foreign keys not being created for some reason...
Hello there!
Once again I have a question and I don't see what I am doing wrong yet. For some reason I get an error back on running migrations, it states:
errno: 150 "Foreign key constraint is incorrectly formed"
For what I've found, that would mean most of the time that the types don't match from the referencing tables. But in my case, they actually do. The schema's are as follows:
Schema::create('users', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
Yes, the standard user table! ;) And the other table is this one:
Schema::create('articles', function (Blueprint $table) {
// Create table columns
$table->bigIncrements('id');
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('type_id');
$table->dateTimeTz('published_at')->nullable()->default(null);
$table->dateTimeTz('unpublished_at')->nullable()->default(null);
$table->longText('content');
$table->timestamps();
$table->softDeletes();
// Set foreign keys for articles table
$table->foreign('user_id')
->references('id')->on('users')
->onDelete('set null');
$table->foreign('type_id')
->references('id')->on('types')
->onDelete('set null');
});
So you could say, an almost copy of Jeffrey Way's tutorial! And yes, I do the exact same thing, but yet I still get the error message as stated above.
Even the collation and such are the same, what am I forgetting, as I don't see it at all. Keep in mind, both tables are created fresh when I run the migrations and I've already made sure that the order in which the migrations get run are also correct, by having adjusted the timestamps in the filenames.
I use MariaDB 10.3.20-MariaDB-1 from Debian default repositories and the complete configuration is as follows:
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
It's the default connection settings indeed. ;) So I don't understand what goes wrong now? Any help would be welcome! The only thing I can think of is I try to create the foreign key the other way around, but that would suggest Jeffrey Way would do it wrong to in his tutorial (https://laracasts.com/series/laravel-6-from-scratch/episodes/30) at 8 minutes 12 seconds. Which seems unlikely to me, so I must miss something... The Laravel version I use is 6.7.0.
Level 73
@cptchaosnl by both tables you mean both users and types table? Make sure that on your types table the ID is also of type Big Integer
$table->bigIncrements('id');
In case it is not then this:
$table->unsignedBigInteger('type_id');
should be:
$table->unsignedInteger('type_id');
Level 1
@tykus: The users and types tables are created before the articles table indeed!
The $table->bigIncrements('column') function actually does this:
public function bigIncrements($column)
{
return $this->unsignedBigInteger($column, true);
}
So I would think that referencing columns should use $table->unsignedBigInteger('column') like I do now. So I don't understand why that would be $table->unsignedInteger()? What am I missing?
Level 73
@cptchaosnl if in your types table the id column is:
$table->increments('id');
Then the type is Integer, not a Big Integer.. so you should use this:
$table->unsignedInteger('type_id');
If it is bigIncrements then it is Big Integer, then just ignore what I just said :)
I've seen this error before, and it can happen because of using different data types, so that's why I am suggesting to check.
Level 1
@nakov My types table is as follows:
Schema::create('types', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('type')->unique();
$table->timestamps();
});
So unfortunatly also uses $table->bigIncrements('id')...
Level 104
Can you isolate the issue to just one of the foreign key constraints? Or does it occur for both (having removed the other)?
Level 1
@tykus Also when I reduce the foreign keys to one in the script, the error remains. Will retest it though.
EDIT: Just did a retest, the problem indeed remains.
Level 104
So in summary, your migrations are running in the order users, types then articles; your PK columns all are unsigned binInt, and the foreign key columns also are unsigned bigInt types?
Can you confirm that the same storage engine is being used to create the tables, and that storage engine supports foreign keys?
You can force the engine in your database config, or in each migration:
Schema::create('users', function (Blueprint $table) {
$table->engine = 'InnoDB';
// the column definitions
Level 1
For what it's worth: Running the query which Eloquent generated in an IDE like PHPStorm I also get the error. The query generated is
alter table `articles` add constraint `articles_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete set null
I am running PHP version 7.3.12-1, also from the Debian default repositories.
Level 1
@tykus Great answer! And yes, your summary is correct!
I see that in the configuration the engine has been set to null, so I will edit that and set it to InnoDB to ensure everytime InnoDB will be used as storage engine!
EDIT: My configuration for MySQL is now like this:
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => 'InnoDB',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
And the error remains. Will try when I set the engine per table.
EDIT2:
And even after setting $table->engine = 'InnoDB'; per migration / table it also returns the same error.
Level 104
What a mess... last one before bed... can you run the migrations fresh without the FK constraints, then create a separate migration for the FK constraints only; run that using the --pretend option and run SQL result directly in your MySQL GUI. Finally get the status of a SHOW ENGINE INNODB STATUS; query and see if there are any clues there, especially in the LATEST FOREIGN KEY ERROR section of the output - it will typically be more descriptive than the generic errno 150...
1 like
Level 1
@tykus Thanks for the new tips!
When I run the migrations without the foreign keys, the migrations run fine!
I just did the SHOW ENGINE INNODB STATUS query you asked, this was the output:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-12-17 00:52:59 0x7f0e20111700 Error in foreign key constraint of table `screenarts`.`articles`:
Alter table `a-redacted-database-name`.`articles` with foreign key constraint failed. You have defined a SET NULL condition but column 'user_id' is defined as NOT NULL in ' foreign key (`user_id`) references `users` (`id`) on delete set null' near ' on delete set null'.
So I see what's going wrong now! Thanks for all the help and pointing me into the right direction!
I didn't know about the 'show engine' query, major thanks!
Level 104
Aw man... it was right there in the OP and I didn't see it.
At least you got a few new debugging tips from the thread! It's a nice tool to have up your sleeve whenever things are going awry. If that answer helped you, please consider marking it Best Reply.
2 likes
Level 1
For the record; I made the columns nullable and it's indeed working now!
Please or to participate in this conversation.