L5 migrating relationships problem

I am trying to create a relationship betwen two tables in mysql. But when i try to migrate i get this error:

exception 'PDOException' with message '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 ')' at line 1' in /home/mentos1386/PhpstormProjects/EPodjetnik-lar5/vendor/laravel/framework/src/Illuminate/Database/Connection.php:358

Next exception 'Illuminate\Database\QueryException' with message '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 ')' at line 1 (SQL: alter table `users` add constraint users_roles_id_foreign foreign key (`roles_id`) references `roles` ())' in /home/mentos1386/PhpstormProjects/EPodjetnik-lar5/vendor/laravel/framework/src/Illuminate/Database/Connection.php:614

my migration files are:

create_users_table.php

        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('username');
            //$table->integer('roles_id')->unsigned()->index();
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();

            //$table->foreign('roles_id')->reference('id')->on('roles')->onDelete('cascade');
        });

create_roles_table.php

        Schema::create('roles', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->text('comment');
            $table->timestamps();
        });

And then i have relationships.php that creates the commented parts of uper files (i tought this would fix it, but it didnt)

        Schema::table('users', function(Blueprint $table){

            $table->integer('roles_id')->unsigned()->index();
            $table->foreign('roles_id')->reference('id')->on('roles');

        });

(i striped down the irrelevant parts of files) github link if you need.. https://github.com/mentos1386/E-Entrepreneur

bestmomo

11 years ago

Best Answer

Level 52

Hello

You miss a 's' :

 Schema::table('users', function(Blueprint $table){
    $table->integer('roles_id')->unsigned();
    $table->foreign('roles_id')->references('id')->on('roles');
 });

You dont need index méthod because MySql will create an index anyway.

mentos1386

11 years ago

Level 1

Ohh thank you! I was looking at this for a day and couldn’t figure it out why it didn’t work! One question.. Those commented out parts would work as well?

        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('username');
            //$table->integer('roles_id')->unsigned();
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();

            //$table->foreign('roles_id')->references('id')->on('roles')->onDelete('cascade');
        });

or do i have to create separate relationships migration like i did it before?

Please or to participate in this conversation.