SQLSTATE[42000]: Syntax error or access violation: 1072 Key column ‘proform_id’ doesn’t exist in table Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of SQLSTATE[42000]: Syntax error or access violation: 1072 Key column ‘proform_id’ doesn’t exist in table without wasting too much if your time.

The question is published on by Tutorial Guruji team.

After php artisan migrate:fresh I get error:

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column ‘proform_id’ doesn’t exist in table (SQL: alter table proforms add constraint proforms_proform_id_foreign foreign key (proform_id) references proforms (id) on delete cascade)

This is migration which generates error: 2020_08_08_093303_create_dynamic_field.php

<?php

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateDynamicField extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('dynamic_fields', function (Blueprint $table) {
            $table->increments('id');
            $table->string('id_pozycji')->nullable();
            $table->string('name')->nullable();
            $table->string('PKWIU')->nullable();
            $table->integer('quantity')->nullable();
            $table->integer('unit')->nullable();
            $table->integer('netunit')->nullable();
            $table->integer('nettotal')->nullable();
            $table->integer('VATrate')->nullable();
            $table->integer('grossunit')->nullable();
            $table->integer('grosstotal')->nullable();
            $table->integer('proform_id')->nullable();
            $table->timestamps();
            $table->time('deleted_at')->nullable();
        });

        Schema::table('proforms', function (Blueprint $table){
            $table->foreign('proform_id')
                  ->references('id')
                  ->on('proforms')
                  ->onDelete('cascade');            
        });





    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('dynamic_fields');
    }
}

This is migration asociated with it: 2020_07_29_101958_proforms.php

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class Proforms extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('proforms', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('proformnumber')->nullable();
            $table->date('proformdate')->nullable();
            $table->date('selldate')->nullable();
            $table->integer('user_id')->unsigned()->nullable();
            $table->integer('form_id')->unsigned()->nullable();                        
            $table->integer('currency_id')->unsigned()->nullable();
            $table->string('paymentmethod')->nullable();
            $table->date('paymentdate')->nullable();
            $table->string('status')->nullable();
            $table->string('comments')->nullable();
            $table->string('city')->nullable();            
            $table->string('autonumber')->nullable();
            $table->integer('automonth')->nullable();
            $table->integer('autoyear')->nullable();
            $table->string('name')->nullable();
            $table->string('PKWIU')->nullable();
            $table->integer('quantity')->nullable();
            $table->integer('unit')->nullable();
            $table->integer('netunit')->nullable();
            $table->integer('nettotal')->nullable();
            $table->integer('VATrate')->nullable();
            $table->integer('grossunit')->nullable();
            $table->integer('grosstotal')->nullable();
            $table->timestamps();
            $table->time('deleted_at')->nullable();
        });
        
        Schema::table('proforms', function (Blueprint $table){
            $table->foreign('user_id')
                  ->references('id')
                  ->on('users')
                  ->onDelete('cascade');
            
            $table->foreign('form_id')
                  ->references('id')
                  ->on('forms')
                  ->onDelete('cascade');                
    

            $table->foreign('currency_id')
                  ->references('id')
                  ->on('currencys')
                  ->onDelete('cascade');
            
            
            
            
            
            
        });
    

Answer

This looks odd to me:

Schema::table('proforms', function (Blueprint $table) {
    $table->foreign('proform_id')
          ->references('id')
          ->on('proforms')
          ->onDelete('cascade');            
});

It looks like you are trying to add a foreign key with a reference to it’s own table.

There is no proform_id on the proforms table. This statement needs to be run on the dynamic_fields table.

Schema::table('dynamic_fields', function (Blueprint $table) {
    $table->foreign('proform_id')
          ->references('id')
          ->on('proforms')
          ->onDelete('cascade');            
});
We are here to answer your question about SQLSTATE[42000]: Syntax error or access violation: 1072 Key column ‘proform_id’ doesn’t exist in table - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji