Adding a foreign key to my migrations

i am student and trying to use laravel for my final year project. i am trying to create a foreign key on a column in the schedules table in my DB,

here is my code for the schedules table:

    Schema::create('schedules', function (Blueprint $table) {
        $table->increments('schedule_id');
        $table->string('bus_number')->unsigned();
        $table->foreign('bus_reg_number')->references('bus_reg_number')->on('buses');
        $table->string('departure_location');
        $table->string('destination_location');
        $table->dateTime('departure_time');
        $table->date('departure_date');
        $table->string('bus_model');
        $table->integer('number_of_seats');
        $table->float('price', 8, 2);
        $table->timestamps();
    });

this is the error i get when i run php artisan migrate:

In Connection.php line 664:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'unsigned not null, departure_locatio n varchar(191) not null, destination_loca' at line 1 (SQL: create tableschedules(schedule_idint unsigned n ot null auto_increment primary key,bus_numbervarchar(191) unsigned not null,departure_locationvarchar(191) not null,destination_locationvarchar(191) not null,departure_timedatetime not null,departure_datedate n ot null,bus_modelvarchar(191) not null,number_of_seatsint not null,pricedouble(8, 2) not null,created _attimestamp null,updated_at` timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci)

In Connection.php line 452:

thank you.

BezhanSalleh

8 years ago

Level 25

You have a syntax error, change the following to lines :

$table->string('bus_number')->unsigned();

$table->foreign('bus_reg_number')->references('bus_reg_number')->on('buses');

To:

$table->integer('bus_number')->unsigned();

$table->foreign('bus_number')->references('bus_reg_number')->on('buses');

then you will be good to go...

deladels

8 years ago

Level 4

still didnt work. bus numbers are made p of letters and numbers so it cant be an integer

BezhanSalleh

8 years ago

Level 25

The foreign key column must have the same data type, the same scale and the same length as the corresponding referenced column. so what is the data type of bus_reg_number on the buses table, is it really the buses primary key? plus the buses table should be migrated before schedules table.

if you follow the following example your problems will be solved. if not share both of your migrations?

your buses table should look something like this:
Schema::create('buses', function (blueprint $table) {
    $table->increments('id');
    $table->string('reg_number');
   //other fields
});

//schedules
Schema::create('schedules', function (Blueprint $table) {
   $table->increments('id');//schedule_id
   $table->integer('bus_id')->unsigned();
   $table->foreign('bus_id')->references('id')->on('buses');
  //other fields
});

deladels

8 years ago

Level 4

buses migration file:

Schema::create('buses', function (Blueprint $table) { //$table->engine = 'InnoDB'; $table->increments('id'); $table->string('reg_number'); $table->integer('capacity'); $table->string('model'); $table->timestamps(); });

schedule migration file:

Schema::create('schedules', function (Blueprint $table) { //$table->engine = 'InnoDB'; $table->increments('id'); $table->string('bus_id')->unsigned(); $table->foreign('bus_id')->references('id')->on('buses'); $table->string('departure_location'); $table->string('destination_location'); $table->dateTime('departure_time'); $table->date('departure_date'); $table->integer('number_of_seats'); $table->float('price', 8, 2); $table->timestamps(); });

BezhanSalleh

8 years ago

Best Answer

Level 25

Delete your buses and schedules migration files, then first create the buses migration and update it as following buses migration. second; create the schedules migration and update it as following schedules migration. then you will be good to go.

 buses migration file:
Schema::create('buses', function (Blueprint $table) { 
    //$table->engine = 'InnoDB'; 
    $table->increments('id'); 
    $table->string('reg_number'); 
    $table->integer('capacity'); 
    $table->string('model'); 
    $table->timestamps(); 
});

schedule migration file:

Schema::create('schedules', function (Blueprint $table) { 
    //$table->engine = 'InnoDB'; 
    $table->increments('id'); 
    $table->integer('bus_id')->unsigned(); 
    $table->foreign('bus_id')
        ->references('id')
        ->on('buses')
        ->onDelete('cascade'); 
    $table->string('departure_location'); 
    $table->string('destination_location'); 
    $table->dateTime('departure_time'); 
    $table->date('departure_date'); 
    $table->integer('number_of_seats'); 
    $table->float('price', 8, 2); 
    $table->timestamps(); 
});

Please or to participate in this conversation.