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
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();
});
'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.