what does sqlstate[42703] mean on heroku?

Question

I have created a laravel app and everything is working as it should on my localhost. I am using XAMPP and PHPMyAdmin to test the application locally and when I uploaded it to Heroku and configured the database and run the "heroku run php artisan migrate" without errors so far.

My application is a simple form where the user registers information and it is stored in the database. the database consists of some tables. here is the migration for the users table

  public function up()
  {
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('lastname');
        $table->string('username')->uniqe();
        $table->string('ssn');
        $table->string('lastfour');
        $table->unique(array('ssn','lastfour'));
        $table->string('email')->unique();
        $table->string('role')->default('Applicant');
        $table  ->foreign('role')
                ->references('name')
                ->on('role');
        $table->date('applicationDate');
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->boolean('application_result')->nullable();
        $table->rememberToken();
        $table->timestamps();
    });
}

and the competence_profile migration

public function up()
{
    Schema::create('competence_profile', function (Blueprint $table) {
        $table->increments('competence_profile_id');
        $table->unsignedInteger('person_id');
        $table  ->foreign('person_id')
                ->references('id')
                ->on('users');

        $table->String('competence');
        $table  ->foreign('competence')
                ->references('name')
                ->on('competence');
        $table->integer('years_of_experience');
        $table->timestamps();
    });
}

however, I got the error when I submit the form to the database only on Heroku. as mentioned it works fine locally and I can see the data stored in phpmyadmin. here is the error exception is shown from Heroku.

SQLSTATE[42703]: Undefined column: 7 ERROR: column "id" does not exist LINE 1: ...t", "created_at") values ($1, $2, $3, $4, $5) returning "id" ^ (SQL: insert into "competence_profile" ("person_id", "competence", "years_of_experience", "updated_at", "created_at") values (4, Designer, 6, 2019-02-22 11:36:29, 2019-02-22 11:36:29) returning "id").

I would appreciate any explanation or help about this error. it is basically not finding the column even though it is defined in the migration.

Answer 1

I found a simple solution to the SQLState[42703] which you get from heroku. When you have a table in the database, heroku assumes that the primary key for that table is called 'id'. If you have a primary key that is called other than that (in this case the primary key is called competence_profile_id) you need to add a variable in the model with the value of the primary key that you have. I have named the variable in my competence_profile model as follows $primaryKey = 'competence_profile_id'; and that fixed the problem