Laravel Migration Failed

Question

I'm having a bit of trouble with Laravel Migrations. I've recently stopped developing on C9 and decided to try out Nitrous.io instead - however I'm unsure whether my error is related to this. I also decided to change from sqlite to mysql. Since I'm a bit of an SQL Noob; I'm having trouble figuring this out.

I receive this error:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unsigned not null, `tag_id` varchar(255) unsigned
not null, `created_at` timesta' at line 1 (SQL: create table `yoyo_tag` (`id` int unsigned not null auto_increment primary key, `yoyo_id` varchar(255) unsigned not null, `tag_id` varchar(255) unsigned not null, `created_at` timestamp defau
lt 0 not null, `updated_at` timestamp default 0 not null) default character set utf8 collate utf8_unicode_ci)

and also this:

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unsigned not null, `tag_id` varchar(255) unsigned
not null, `created_at` timesta' at line 1

My migration is as below:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateYoyoTagsTable extends Migration {

    /**
      * Run the migrations.
      *
      * @return void
      */
    public function up()
    {
        Schema::create('yoyo_tag', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('yoyo_id')->unsigned()->index();
            $table->foreign('yoyo_id')->references('id')->on('yoyo')->onDelete('cascade');

            $table->string('tag_id')->unsigned()->index();
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('yoyo_tag');
    }

}

The create table statement is below:

create table `yoyo_tag` (`id` int unsigned not null auto_increment primary key, `yoyo_id` varchar(255) unsigned not null, `tag_id` varchar(255) unsigned not null, `created_at` timestamp default 0 not null, `updated_at` times
tamp default 0 not null) default character set utf8 collate utf8_unicode_ci

I added the create table statement to the post as requested. — Kim Ward, Nov 15 '15 at 21:00

score 2 · Accepted Answer · answered Nov 15 '15 at 21:23

Looking at your migration, yoyo_id and tag_id should be rather unsigned integers and not unsigned string. In down method you should also remove foreign keys before deleting your table so valid migration should look like this:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateYoyoTagsTable extends Migration {

    /**
      * Run the migrations.
      *
      * @return void
      */
    public function up()
    {
        Schema::create('yoyo_tag', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('yoyo_id')->unsigned()->index();
            $table->foreign('yoyo_id')->references('id')->on('yoyo')->onDelete('cascade');

            $table->integer('tag_id')->unsigned()->index();
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
         Schema::table('yoyo_tag', function ($table) {
            $table->dropForeign('yoyo_tag_yoyo_id_foreign');
            $table->dropForeign('yoyo_tag_tag_id_foreign');
        });


        Schema::drop('yoyo_tag');
    }

}

Thanks for your help; unfortunately that was followed up with another errors. This time relating the foreign key. Any ideas? [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table 'homestead.#sql-2d37_4e' (errno: 150) (SQL: alter table `yoyo_tag` add constraint yoyo_tag_yoyo_id_foreign foreign key (`yoyo_id`) references `yoyo` (`id`) on delete cascade) — Kim Ward, Nov 15 '15 at 22:01
Ignore my comment I've finally corrected it and all is well! I'd misnamed the table 'yoyo' it was 'yoyos'... such a stupid mistake. — Kim Ward, Nov 15 '15 at 22:17

Laravel Migration Failed

1 Answers1