phpmysqllaraveldatabasebackend

(Laravel 11) errno: 150 "Foreign key constraint is incorrectly formed


I need to do a relation between the tables citizen and nuclear_Family, this is intended so they can fulfill a form of their family information, that will be linked to a citizen folder that works with their identification number referred as 'Cedula', the identification is being stored as a string in a MySQL database

Schema::create('nuclear_family', function (Blueprint $table) {
            $table->id();
            $table->integer('familyNumber');
            $table->string('name');
            $table->string('middlename');
            $table->string('surname');
            $table->string('secondsurname');
            $table->integer('identification');
            $table->integer('bornyear');
            $table->integer('age');
            $table->string('kinship');
            $table->string('occupation');
            $table->string('workplace');
            $table->string('income');
            $table->string('ailments');
            $table->string('totalFamilyIncome');
            $table->string('familyHeadIncome');
            $table->string('totalIncome');
            $table->integer('familyId');
            $table->string('mainCedula');
            $table->foreign('mainCedula')->references('cedula')->on('citizen');
            $table->timestamps();
        });

This is the schema migration for the nuclear_Family form

Schema::create('citizen', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('middlename');
            $table->string('surname');
            $table->string('secondsurname');
            $table->string('age');
            $table->string('phone');
            $table->string('cedula');
            $table->timestamps();
        });

and this is the schema migration for citizen, whenever I try to do a migration, it fails to add mainCedula as a foreign key of 'cedula' on the table citizen despite both being strings, this is the following error when doing a migration with php artisan migrate

error when doing the migrate command

I tried working with the foreignId and constrained methods as the documentations dictates to see if I could solve the error but it didn't worked

Schema::create('nuclear_family', function (Blueprint $table) {
            $table->id();
            $table->integer('familyNumber');
            $table->string('name');
            $table->string('middlename');
            $table->string('surname');
            $table->string('secondsurname');
            $table->integer('identification');
            $table->integer('bornyear');
            $table->integer('age');
            $table->string('kinship');
            $table->string('occupation');
            $table->string('workplace');
            $table->string('income');
            $table->string('ailments');
            $table->string('totalFamilyIncome');
            $table->string('familyHeadIncome');
            $table->string('totalIncome');
            $table->integer('familyId');
            $table->foreignId('mainCedula')->constrained(
                table: 'citizen', indexName: 'nufam_cedula');
            $table->timestamps();
        });
Schema::create('citizen', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('middlename');
            $table->string('surname');
            $table->string('secondsurname');
            $table->string('age');
            $table->string('phone');
            $table->foreignId('cedula');
            $table->timestamps();
        });

Solution

  • 'cedula' should be unique in the citizen table to ensure integrity when linking to nuclear_family.

    You can update your migration like this,

    In Citizen table migration Update $table->string('cedula')->unique();

    In Nuclear Family Table migration Update $table->foreign('mainCedula')->references('cedula')->on('citizen')->onDelete('cascade');

    After adjusting the migration files as above, run the migration command again.