Hi tklement,

You need to add the unsigned() function to the foreign keys that references to the fields in the employee table as follow:

public function up()
{
//first define the employees table. i don't know how many fields you have but i simulate it.
      Schema::create('employees', function(Blueprint $table)
     {
                $table->increments('employee_id');
                $table->string('name');
                $table->timestamp();
     }

//second define the bookings table with the foreign keys

    Schema::create('bookings', function(Blueprint $table)
    {
        $table->increments('booking_id');
        $table->foreign('employee_id')->references('employee_id')->on('employees'); //also can be defined the ondelete and onupdate behaviours
        $table->foreign('service_id')->references('service_id')->on('services');.
        $table->timestamp('start_time'); //what will be the focus of this field???
        $table->timestamps();
    });
}

Later you need to add the relation in both Models with hasMany and belongsTo.

Note. You must add the unsigned to the foreign key field because the integer field is defined as signed and the foreign key could give you some error relating these two tables.

Hope it helps you.

Hi codeATbusiness,

thank you for your help but I guess I'm unable to fix it with that or it isn't the solution.

To complete my tables here are the connected tables:

Employees

public function up()
{
	Schema::create('employees', function(Blueprint $table)
	{
		$table->increments('employee_id')->unsigned();
		$table->string('firstname', 100);
		$table->string('lastname', 100);
		$table->binary('picture');
		$table->timestamps();

		$table->unique(array('firstname', 'lastname'));
	});
}

Services

public function up()
{
	Schema::create('services', function(Blueprint $table)
	{
		$table->increments('service_id')->unsigned();
		$table->string('name', 100);
		$table->decimal('price', 5, 2);
		$table->integer('duration');
		$table->timestamps();

		$table->unique('name');
	});
}

Employee_Service

public function up()
{
	Schema::create('employee_service', function(Blueprint $table)
	{
		$table->increments('employee_service_id')->unsigned();
		$table->timestamps();
	});
}

Add_Foreign_Key_To_Booking_Table

public function up()
{
	Schema::table('bookings', function(Blueprint $table)
	{
		$table->foreign('booking_id')->references('employee_id')->on('employees');
// AND HERE IS THE PROBLEM.
// THE SECOND KEY WON'T ACCEPT BY THE SYSTEM
		$table->foreign('booking_id')->references('service_id')->on('services');
	});
}

In the last Schema the second foreign key returns an error while executing. "SQLSTATE[23000]: Integrity constraint violation: Can't write; duplicate key in table '#sql-1208_1e2' "

Isn't it possible to create more then one relation with the same foreign key ?

Hi tklement,

When you add the reference to the employee_service table for the others table (employee and service) you need to add two references, one for each table, because it is a pivot table as follow:

$table->integer('employee_id')->unsigned();
$table->integer('service_id')->unsigned();

Don't use increments, it is for autoincrement the value as you insert it but you don't need because the values are related with the others table. Only use increments for the Primary Keys of your main tables.

Later add the foreign keys as you did it.

I'm so sorry and thank you for your patience but I don't understand it. When I call "php artisan migrate" the script stopped while it tries to do this here:

alter table `bookings` add constraint bookings_booking_id_foreign foreign key (`booking_id`) references `services` (`service_id`)

This line has to be the second of that script:

public function up()
{
	Schema::table('bookings', function(Blueprint $table)
	{
		$table->foreign('booking_id')->references('employee_id')->on('employees');
		$table->foreign('booking_id')->references('service_id')->on('services');
	});
}

I really have no clue where I shall fix my code.

H tklement,

Don't worry, you are welcome!

You are duplicating your foreignkey definition because you call both as booking_id. You need to asign two different FK_names as follow:

public function up()
{
Schema::table('bookings', function(Blueprint $table)
{
    $table->foreign('employee_id')->references('employee_id')->on('employees');
    $table->foreign('service_id')->references('service_id')->on('services');
});

}

Hope it helps you.

Oh no. This is it? Now you have opened my eyes! I always thought I had to create the foreign key with the same name or column. And also I thought I don't need to create e.g. columns for foreign keys because the system would create them as they are 'foreign keys'.

Now when I run the migration everything works fine.

Thank you so much!

Sorry maybe i explain bad in the previous post and put the same name to the Foreign keys.