Framework: Multiple primary keys with one auto increment

[ScreamingDev]

• Laravel Version: 5.3.26
• PHP Version: 7.0
• Database Driver & Version: MySQL

Description:

This is the target query:

mysql> CREATE TABLE mytable (
    ->     table_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     database_id MEDIUMINT NOT NULL,
    ->     other_column CHAR(30) NOT NULL,
    ->     PRIMARY KEY (database_id,table_id)
    -> ) ENGINE=MyISAM;

In that way you can have some scopes which do:

mysql> INSERT INTO mytable (database_id, other_column) VALUES
    ->     (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable ORDER BY database_id,table_id;
+----------+-------------+--------------+
| table_id | database_id | other_column |
+----------+-------------+--------------+
|        1 |           1 | Foo          |
|        2 |           1 | Bar          |
|        3 |           1 | Bam          |
|        1 |           2 | Baz          |
|        2 |           2 | Zam          |
|        1 |           3 | Zoo          |
+----------+-------------+--------------+
6 rows in set (0.00 sec)

Steps To Reproduce:

Try it using migrations. I didn't manage to make it work.

I am stuck. Laravel / Eloquent does not put the primary key and auto increment in one query.

I tried it like this:

$table->integer('wallet_id')->unsigned();
            $table->bigInteger('reference_id', true, true); // auto increment here
            $table->string('title');
            $table->integer('coins');
            $table->timestamps();
            $table->softDeletes();
            $table->primary(['wallet_id', 'reference_id']);

Leads to 2 queries which end up in this error:

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple pr  
  imary key defined

Because the second query tries to add a primary key.

[soprun]

As an example

Schema::create('product', function (Blueprint $table) {
    $table->engine = 'MyISAM';
    $table->unsignedInteger('store_id');
    $table->unsignedInteger('id');
    $table->primary(['store_id', 'id']);
    // ...
});
Schema::table('product', function (Blueprint $table) {
    $table->integer('id', true, true)->change();
});

[ScreamingDev]

Dang it. Thanks @vlsoprun . This works as planned.
Not in one query but it works.

[themsaid]

Glad you could get it to work.

[dazes97]

@vlsoprun thank you! it worked!