CODEWITHSUNDEEP
laravel-5.8composite-primary-keymysql-error-1068
New Atech
New Atech

Reputation: 11

Multiple primary keys with one auto increment laravel

I have to migrate this database with Laravel to MySQL, but it seems that I can't use multiple primary key for autoincrements elements.

 public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('user_id');
        $table->string('name');
        $table->string('surname');
        $table->string('email')->unique();
        $table->string('tel');
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });

    Schema::create('collections', function (Blueprint $table) {
        $table->unsignedBigInteger('user_id');
        $table->foreign('user_id')->references('user_id')->on('users');
        $table->bigIncrements('collection_id');

        $table->primary(['user_id', 'collection_id']);
        $table->string('title');
        $table->string('img_url');
        $table->timestamps();
    });

    Schema::table('collections', function (Blueprint $table) {
        $table->dropPrimary('collections_collection_id_fk_primary');
    });

    Schema::create('contents', function (Blueprint $table) {
        $table->unsignedBigInteger('collection_id');
        $table->foreign('collection_id')->references('collection_id')->on('collections');
        $table->bigIncrements('content_id');

        $table->primary(['content_id', 'collection_id']);
        $table->string('title');
        $table->string('author');
        $table->string('publisher');
        $table->string('pages');
        $table->string('google_id');
        $table->string('img_url');
        $table->timestamps();
    });


Schema::table('collections', function (Blueprint $table){
    $table->dropPrimary('contents_content_id_fk_primary');
});
}

I've tried to use dropPrimary but it doesn't execute the query, because it stops at the error: " Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table collections add primary key collections_user_id_collection_id_primary(user_id, collection_id))"

Can you help me?

Upvotes: 1

Views: 1772

Answers (1)

Paul K.
Paul K.

Reputation: 21

TLDR: The following column definition already automatically creates a primary key:

$table->bigIncrements('collection_id');

Therefore your explicit call to $table->primary() tries to create a second primary key.

Long answer

I've encountered a similar problem. I had a composite primary key on two columns which caused problems when I started to queue jobs because the framework serialized only the $primaryKey of the model (you can't define two columns as $primaryKey in models. So I added an auto increment primary column to the migration:

$this->bigIncrements('id')->primary();

This caused the error message which you are getting. After removing the primary() call it worked and I noticed that the primary key got automatically set. Instead I am now using a composite unique key:

$table->unique(['column_a', 'column_b']);

I hope this helps in your case.

Upvotes: 2

Related Questions