I have the following relationship diagram erd
green highlighted text represent primary key and yellow highlighted text represent foreign key,as you can see the foreign key are non primary key
I have created this three table but getting this error while migration
SQLSTATE[HY000]: General error: 1005 Can't create table `project-whirlpool`.`mother_meters` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `mother_meters` add constraint `mother_meters_assign_hrid_foreign` foreign key (`assign_hrid`) references `tenants` (`hrid`))
Here is my Tenant table
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateTenantsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('tenants', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('nid');
$table->string('nid_img');
$table->string('phone');
$table->string('exp_rent');
$table->string('paid_rent');
$table->string('dues');
$table->string('pay_date');
$table->string('comment');
$table->integer('hrid');//home or room number
$table->boolean('status');
$table->date('exit');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('tenants');
}
}
Mother Meter table
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateMotherMetersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
//protected $fillable = ['meter_number','hrid','type','consume_unit','bill_amount','year','month','pay_status'];
public function up()
{
Schema::create('mother_meters', function (Blueprint $table) {
$table->increments('id');
$table->integer('meter_number');
$table->unsignedInteger('assign_hrid'); //home or room number
$table->string('type');
$table->string('consume_unit');
$table->string('bill_amount');
$table->string('year');
$table->string('month');
$table->string('pay_status');
$table->timestamps();
$table->foreign('assign_hrid')->references('hrid')->on('tenants');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('mother_meters');
}
}
Sub Meter table
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateSubMetersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
//protected $fillable = ['meter_number','hrid','type','prev_reading','curr_reading','consume_unit','bill_amount','year','month','pay_status'];
public function up()
{
Schema::create('sub_meters', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('assign_meter_num');
$table->unsignedInteger('rid');//home or room number
$table->string('type');
$table->string('prev_reading');
$table->string('curr_reading');
$table->string('consumeny_unit');
$table->string('bill_amount');
$table->string('year');
$table->string('month');
$table->string('pay_status');
$table->timestamps();
$table->foreign('assign_meter_num')->references('meter_number')->on('mother_meters');
$table->foreign('rid')->references('assign_hrid')->on('mother_meters');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('sub_meters');
}
}
