My present table is
Schema::create('students', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('first_name', 255);
$table->string('last_name', 255);
$table->enum('gender', ['m', 'f']);
$table->date('date_of_birth');
$table->integer('roll_number');
$table->char('section', 1);
$table->integer('class');
$table->unsignedBigInteger('school_id');
$table->string('photo')->nullable;
$table->timestamps();
$table->foreign('school_id')
->references('id')->on('schools')
->onUpdate('cascade')->onDelete('cascade');
$table->unique(['roll_number', 'section', 'class', 'school_id']);
});
Standards
Schema::create('standards', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->unsignedBigInteger('school_id');
$table->timestamps();
$table->foreign('school_id')
->references('id')->on('schools')
->onUpdate('cascade')->onDelete('cascade');
});
Sections
Schema::create('sections', function (Blueprint $table) {
$table->bigIncrements('id');
$table->char('name', 1);
$table->unsignedBigInteger('standard_id');
$table->timestamps();
$table->foreign('standard_id')
->references('id')->on('standards')
->onUpdate('cascade')->onDelete('cascade');
});
now I have standards and sections table and foreign keys from these tables will replace the class and section columns in existing structure and maintain the combination of roll_number
, section_id
, standard_id
and school_id
as unique.
I tried
public function up()
{
Schema::table('students', function (Blueprint $table) {
$table->dropUnique(['roll_number', 'section', 'class', 'school_id']);
$table->dropColumn('section');
$table->dropColumn('class');
$table->unsignedBigInteger('standard_id')->after('roll_number');
$table->unsignedBigInteger('section_id')->after('standard_id');
$table->foreign('standard_id')->references('id')->on('standards')
->onUpdate('cascade')->onDelete('cascade');
$table->foreign('section_id')->references('id')->on('sections')
->onUpdate('cascade')->onDelete('cascade');
$table->unique(['roll_number', 'standard_id', 'section_id', 'school_id']); // unique combination
});
}
but it seems to be not working.
Error
Illuminate\Database\QueryException : SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
myapp_extra
.#sql-2f78_29d
, CONSTRAINTstudents_standard_id_foreign
FOREIGN KEY (standard_id
) REFERE NCESstandards
(id
) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: alter tablestudents
add constraintstudents_standard_id_foreign
foreign key (standard_id
) referencesstandards
(id
) on delete cascade on update cascade)at \myapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664
Note: standards and sections table are created, before making this migration, so both columns are available.
sections
andstandards
schema? – Milad Barazandeh