Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel errno: 150 "Foreign key constraint is incorrectly formed

I am creating foreign keys of party_id and employee_id. it giving me error

  Schema::table('ledger_accounts', function (Blueprint $table) {
        $table->enum("type", ["simple", "bank", "party", "employee"])->after("id")->default("simple");            
        $table->unsignedBigInteger("party_id")->nullable()->default(null)->after("account_type");
        $table->unsignedBigInteger("employee_id")->nullable()->default(null)->after("party_id");
        $table->string("bank_name", 120)->nullable()->after("employee_id");
        $table->string("bank_branch_name", 120)->nullable()->after("bank_name");
        $table->string("bank_branch_address", 120)->nullable()->after("bank_branch_name");
        $table->string("bank_branch_ifsc", 30)->nullable()->after("bank_branch_address");
        $table->string("bank_account_no", 30)->nullable()->after("bank_branch_ifsc");
        $table->string("comments", 255)->nullable()->after("bank_account_no");
        $table->boolean("is_active")->default(1)->after("comments");
        $table->bigInteger("created_by")->nullable();
        $table->bigInteger("updated_by")->nullable();
    });

    Schema::table('ledger_accounts', function (Blueprint $table) {
        $table->foreign('party_id')->references('id')->on('parties')->onDelete('restrict');
        $table->foreign('employee_id')->references('id')->on('employees')->onDelete('restrict');
    });

Below is Screenshots of database structure of two tables

enter image description here

enter image description here

It Gives me error

SQLSTATE[HY000]: General error: 1005 Can’t create table wc_curtis.ledger_accounts (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter
table ledger_accounts add constraint ledger_accounts_party_id_foreign foreign key (party_id) references parties (id) on delete restrict)

Tags:

2

Answers


  1. 0

    The error may be due to the party_id and employee_id columns being of type unsignedBigInteger; they need to match the data type of the id columns in the parties and employees tables. Laravel automatically creates indexes for foreign key columns, but you can add them explicitly for better clarity.

    Schema::table(‘ledger_accounts’, function (Blueprint $table) {
    $table->unsignedBigInteger("party_id")->nullable()->default(null)->after("account_type");
    $table->unsignedBigInteger("employee_id")->nullable()->default(null)->after("party_id");

    $table->index('party_id');
$table->index('employee_id');

    });

    Schema::table(‘ledger_accounts’, function (Blueprint $table) {
    $table->foreign(‘party_id’)->references(‘id’)->on(‘parties’)->onDelete(‘restrict’);
    $table->foreign(’employee_id’)->references(‘id’)->on(’employees’)->onDelete(‘restrict’);
    });

    Login or Signup to reply.
  2. 0

    You can achieve all the table definitions in one schema declaration rather than two.

    Schema::table('ledger_accounts', function (Blueprint $table) {
    $table->enum("type", ["simple", "bank", "party", "employee"])->after("id")->default("simple");            
    $table->unsignedBigInteger("party_id")->nullable()->default(null)->after("type");
    $table->unsignedBigInteger("employee_id")->nullable()->default(null)->after("party_id");
    $table->string("bank_name", 120)->nullable()->after("employee_id");
    $table->string("bank_branch_name", 120)->nullable()->after("bank_name");
    $table->string("bank_branch_address", 120)->nullable()->after("bank_branch_name");
    $table->string("bank_branch_ifsc", 30)->nullable()->after("bank_branch_address");
    $table->string("bank_account_no", 30)->nullable()->after("bank_branch_ifsc");
    $table->string("comments", 255)->nullable()->after("bank_account_no");
    $table->boolean("is_active")->default(1)->after("comments");
    $table->bigInteger("created_by")->nullable();
    $table->bigInteger("updated_by")->nullable();

    $table->foreign('party_id')->references('id')->on('parties')->onDelete('restrict');
    $table->foreign('employee_id')->references('id')->on('employees')->onDelete('restrict');
});

    Also, when defining the party_id, you included after(‘account_type’) on it which is to create the ‘party_id’ column after the ‘account_type’ column which doesn’t exist on this table. I’m guessing you mean to use ‘type’ there or rename the type column to ‘account_type’.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search