Level 1
i used laravel 8 and PHP version 7.4
but why always error. if I use PHP version 8 it doesn't error
i don't know why
Aug 30, 2023
17
Level 1
(errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `users` add constraint `users_role_id_foreign` foreign key (`role_id`) references `roles` (`id`))
so i want create 3 roles on the table, but always error foreign key constraint
table users
<?php
use App\Models\Role;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id(); // primary key
// main data
$table->bigInteger('nidn')->unique(); // bersifat unik
$table->string('fullname');
$table->string('email')->unique();
$table->string('password');
// detail
$table->enum('faculty', ['FTIK', 'FEIS']);
$table->string('major')->nullable();
$table->string('telp')->unique()->nullable();
$table->date('birthdate')->nullable();
$table->enum('gender', ['Pria', 'Wanita']);
$table->text('address')->nullable();
$table->text('image')->nullable();
// identifier
$table->unsignedBigInteger('role_id');
$table->foreign('role_id')->references('id')->on('roles');
// $table->boolean('is_active')->default(true);
// $table->dateTime('last_login')->nullable();
// system
$table->timestamp('email_verified_at')->nullable();
$table->rememberToken();
$table->timestamps();;
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
table roles
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateRolesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('roles', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('roles');
}
}
user model
public function role(){
return $this->belongsTo(Faculty::class, 'role_id', 'id');
}
role model
public function user(){
return $this->hasMany(User::class);
}
Level 50
@mhmmdva Can you show your role table migration file?
Level 1
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateRolesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('roles', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('roles');
}
}
Level 50
Level 104
Probably because the roles table does not exist before you attempt to migrate the users table. If possible, change the timestamp in the filename for the roles table to pre-date the users table migration to ensure there is a roles table to be referenced. Otherwise, make a separate migration to add the foreign key and constraint.
Level 73
Level 1
@Tray2 the error remains the same
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id(); // primary key
// main data
$table->bigInteger('nidn')->unique(); // bersifat unik
$table->string('fullname');
$table->string('email')->unique();
$table->string('password');
// detail
$table->enum('faculty', ['FTIK', 'FEIS']);
$table->string('major')->nullable();
$table->string('telp')->unique()->nullable();
$table->date('birthdate')->nullable();
$table->enum('gender', ['Pria', 'Wanita']);
$table->text('address')->nullable();
$table->text('image')->nullable();
// identifier
$table->unsignedInteger('role_id');
// system
$table->timestamp('email_verified_at')->nullable();
$table->timestamps();;
$table->foreign('role_id')->references('id')->on('roles');
$table->rememberToken();
});
}
Level 104
@mhmmdva please change the filename for the roles table migration. The users table migration is (probably)
2014_10_12_000000_create_users_table; whereas the roles table is some date after 2014_10_12 - do you understand now?
Level 73
@mhmmdva The order the migrations is run matters, you can't run one migration that references a table unless the migration that is creating that table is run first. In short the table must exist in the database before you create the constraint.
1 like
Level 1
@tykus I just found out if the timestamp has an effect. It's resolved thanks
Level 1
@Tray2 OK, thank you, I got new knowledge
Level 104
@mhmmdva yes...
If possible, change the timestamp in the filename for the roles table to pre-date the users table migration to ensure there is a roles table to be referenced
Level 73
Level 104
@Tray2 yes, already covered that earlier
Otherwise, make a separate migration to add the foreign key and constraint.
1 like
Level 1
Please or to participate in this conversation.