4

Using Laravel Migrations, is there a way to create this an MyISAM table with a two-column primary key and an auto-increment in one of them?

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

In this case the id AUTO_INCREMENT is done relative to the value of grp column. And you get something like this:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
Papa Lazzarou
  • 171
  • 6

2 Answers2

3

Well, I managed to do it with two migrations 1. Create the table and assign the PK as usual to the two fields. 2. Create a new migration to modify and add the AUTO_INCREMENT attribute to the id column.

create_animals_table:

Schema::create('animals', function(Blueprint $table) {
    $table->engine = 'MyISAM';
    $table->enum('grp', ['fish', 'mammal', 'bird']);
    $table->unsignedBigInteger('id'); 
    $table->char('name', 30);

    $table->primary(['grp', 'id']);
});

add_auto_increment_to_animals_table:

Schema::table('animals', function ($table) {
    $table->bigIncrements('id')->unsigned()->change();
});
Papa Lazzarou
  • 171
  • 6
1

Try the following:

Schema::create('animals', function(Blueprint $table) {
    $table->engine = 'MyISAM';
    $table->enum('grp', ['fish', 'mammal', 'bird']);
    $table->bigIncrements('id'); // no mediumIncrements
    $table->char('name', 30);
    $table->primary(['grp', 'id']);
});

There isn't a mediumIncrements function so use bigIncrements as next best option.

Niraj Shah
  • 15,087
  • 3
  • 41
  • 60
  • Thanks, but no luck: [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table `animals` add primary key `animals_ grp_id_primary`(`grp`, `id`)) – Papa Lazzarou Oct 27 '16 at 10:44