#Foreign key mismatch after naming ids

7 messages · Page 1 of 1 (latest)

dusty wagon
#

Hi, This was previously working when I had default named ids like $table->id();, but I want to now name my primary keys.
I get the following error and am trying to understand the problem with the foreign key mismatch

    SQLSTATE[HY000]: General error: 1 foreign key mismatch - "source_sentences" referencing "projects" 
    (SQL: insert into "source_sentences" 
    ("grouping_index", "page_num", "project_id", "sentence_text", "user_id") 
    values 
    (0, 1, 1, qwerty., 1), (1, 1, 1, asdf., 1), (2, 1, 1, zcxv., 1))

when trying to insert data here

    DB::table('source_sentences')->insert( $data );

My tables look like this

    public function up()
    {
        Schema::create('projects', function (Blueprint $table) {
            $table->id('project_id');
            $table->string('title', 500);
            $table->string('description', 5000)->nullable();
            $table->char('source_lang', 2);
            $table->timestamps();
            $table->foreignId('user_id')->constrained();
            $table->comment('The name and details of the text being translated.');
        });
    }

and

    public function up()
    {
        Schema::create('source_sentences', function (Blueprint $table) {
            $table->id('source_sentence_id');
            $table->mediumInteger('grouping_index');
            $table->mediumInteger('page_num');
            $table->string('sentence_text', 1000);
            $table->timestamps();
            $table->foreignId('user_id')->constrained();
            $table->foreignId('project_id')->constrained();
            $table->unique(['project_id', 'grouping_index', 'page_num']);
            $table->comment('The original text, broken into sentences.');
        });
    }

After changing the tables, I ran php artisan migrate:fresh which ran successfully. Anyone know what the problem might be?

Thanks.

flat sail
#

$table->foreignId('user_id') is a shortcut that assume your id is called Id. You need to declare your foreign key manually like this - $table->foreign('project_id')->references('project_id')->on('projects');

#

I dunno why you have to change your id column names though.

dusty wagon
#

Migrate fresh seems to have with that syntax.

    public function up()
    {
        Schema::create('source_sentences', function (Blueprint $table) {
            $table->id('source_sentence_id');
            $table->mediumInteger('grouping_index');
            $table->mediumInteger('page_num');
            $table->string('sentence_text', 1000);
            $table->timestamps();
            $table->foreignId('user_id')->constrained();
            $table->foreign('project_id')->references('project_id')->on('projects');
            //$table->foreignId('project_id')->constrained();
            $table->unique(['project_id', 'grouping_index', 'page_num']);
            $table->comment('The original text, broken into sentences.');
        });
    }

SQLSTATE[HY000]: General error: 1 unknown column "project_id" in foreign key definition (SQL: create table "source_sentences" ("source_sentence_id" integer not null primary key autoincrement, "grouping_index" integer not null, "page_num" integer not null, "sentence_text" varchar not null, "created_at" datetime, "updated_at" datetime, "user_id" integer not null, foreign key("user_id") references "users"("id"), foreign key("project_id") references "projects"("project_id")))
flat sail
#

You gotta define the unsigned big int field. That line i gave you just defines the foreign relationship.

dusty wagon
#

So the reason I wanted to name the ids was because I noticed when I queried a bunch of joined tables, the ids of the tables became ambiguous to use in the view. Instead of having to write projects.id AS project_id etc... I wanted to use them as is.