Laravel 5.2 migration foreign keys

Question

I am new to web development in Laravel 5.2 and I have this problem. I have a Users and Jobseekers tables and I am trying to create a foreign key in the Jobseekers table that references the Users table using migration but when I run php artisan migrate I get the error

[Illuminate\Database\QueryException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key cons traint fails (jobsitelara.#sql-1a04_7d, CONSTRAINT jobseekers_user_id_foreign FOREIGN KEY (user_id ) REFERENCES users (id)) (SQL: alter table jobseekers add constraint jobseekers_user_id_foreign fore ign key (user_id) references users (id))

[PDOException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key cons traint fails (jobsitelara.#sql-1a04_7d, CONSTRAINT jobseekers_user_id_foreign FOREIGN KEY (user_id ) REFERENCES users (id))

Here is the migration for create_users_table

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });

and the migration for create_jobseekers_table

public function up()
    {
        Schema::create('jobseekers', function (Blueprint $table) {
            $table->increments('id');
            $table->string('firstname');
            $table->string('lastname');
            $table->enum('gender',array('male','female'));
            $table->date('dateofbirth');
            $table->string('occupation', 150);
            $table->string('educationlevel', 200);
            $table->string('cv', 150);
            $table->string('skills', 200);
            $table->string('address', 200);
            $table->string('phonenumber', 30);
            $table->integer('user_id');
            $table->timestamps();
        });
    }

and the migration to create the foreign key is in a separate file that runs after the create tables migrations have been run here is the add_foreignkey_to_jobseekers_table migration

public function up()
{
    Schema::table('jobseekers', function (Blueprint $table) {
        $table->foreign('user_id')->references('id')->on('users');
    });
}

I made sure that the user_id field in the Jobseekers table is also unsigned. I noticed that the tables are referenced as jobsitelara'.'#sql-1a04_8f' in the error messages, I don't understand what's going on. What else is wrong with my code?

Carlos Adames Carlos Adames · Accepted Answer · 2016-09-15T16:12:35

In Laravel Database Migrations, you need to have clear the order of your migrations and the parent – child relationship between your tables. Therefore, when you create tables, you need to create parent tables first and then child tables.

In the Other hand, when you delete tables, you need to delete child tables first and then parents.

@Martin Bean has an explanation on this topic What is a Parent table and a Child table in Database.

When you run in your terminal php artisan migrate: --options--, your functions up and down will be called respectively depending on the option you pass to migrate. If you order your migrations having parent-child relationships clear, you won’t have this problem.

There are also other situations that you should consider when designing your database, like recursive relationships between tables which @Branko Dimitrijevic explains in this topic Database design for a recursive relationship.

Hope it helps!

Laravel 5.2 migration foreign keys

2 Answers