SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition;

0
votes

Migration File

$table->increments('id');
$table->foreign('user_id')->references('id')->on('users');
$table->foreign('band_id')->references('id')->on('bands');
$table->foreign('genre_id')->references('id')->on('genres');
$table->foreign('cate_id')->references('id')->on('cates');
$table->foreign('type_id')->references('id')->on('types');
$table->integer('status');
$table->date('date');
$table->time('time');
$table->decimal('price');
$table->tinyIncrements('instrument');
$table->string('instrument_detail',255);
$table->timestamps();

After run php artisan migrate

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key (SQL: create table bookings (id int unsigned not null auto_increment primary key, status int not null, date date not null, time time not null, price decimal(8, 2) not null, instrument tinyint unsigned not null auto_increment primary key, instrument_detail varchar(255) not null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci)

And this below

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

2
phplaravelartisan-migrate

2 Answers

0
votes
$table->unsignedTinyInteger('instrument', true);

2nd parameter is bool for auto increment default to false

0
votes

The next sentence:

$table->foreign('user_id')->references('id')->on('users');

just tells the db to make a link between the parent/foreign columns, but in order to do it the column must previously exist, so:

$table->unsignedInteger('user_id'); // first this
$table->foreign('user_id')->references('id')->on('users'); // then this

You should do this for every foreign key.

Note:

Laravel doens't need you to define this links because it doens't make use of this, is just for database consistency sake.