Al-Amin's Blog

Foreign key with migration in Laravel 5.8.17

  Al-Amin Sarker  |   22 May, 2019 |   Laravel |   view 90 times

Laravel is highly advantage php framework in the world. This framework gives us much more facilities to develop a software. Data binding is one of them! Most information about Laravel data bindig you always can get from the documentation. But here you can learn something about FK (Foreign Key) migration.

Here I'll show you how to configure or use Foreign key between two tables. I'll use Laravel default `users` (For authenticate) table and my newly created `web_settings` table.

I'll use `user_id` column for `web_settings` table and store authenticate information(`id`) from the `users` table(Here `id` is the Primary key for `users` table and it's default). So, `user_id` is now foreign key of the `web_settings` table. If not clear about that, please go back and read again...

Now, let's go on your terminal and make the laravel artisan command.


 
	php artisan make:auth


After succeed that migration, you'll see ..._create_users_table.php file in your `database/migrations` folder. The code is here for that file...


 
	public function up()
	{
	    Schema::create('users', function (Blueprint $table) {
	        $table->bigIncrements('id');
	        $table->string('name');
	        $table->string('email')->unique();
	        $table->timestamp('email_verified_at')->nullable();
	        $table->string('password');
	        $table->rememberToken();
	        $table->timestamps();
	    });
	}

Here `id` column has 'bigIncrements' method and of course you mind it that the `id` were used UNSIGNED attributes and it's default by Laravel.

Now, I'm going to create `web_settings` table using laravel artisan command.


 
	php artisan make:migration create_web_settings_table

After succeed that migration, I write code on '..._create_web_settings_table.php' file in same directory.


 
	public function up()
	{
	    Schema::create('web_settings', function (Blueprint $table) {
	        $table->bigIncrements('id');

	        // Must be use bigInteger and unsigned for FK
	        $table->bigInteger('user_id')->unsigned(); 

	        $table->foreign('user_id')->references('id')->on('users');
	        
	        $table->string('title');
	        $table->longText('message');
	        $table->timestamps();
	    });
	}

The corresponding columns in the foreign key(`user_id`) and the referenced key(`id`) must have similar data types. The size and sign of integer types must be the same. That's why I used bigInteger and unsigned methods.

The thing is the `id` field of parent table(users) was built with $table->bigIncrements(‘id’) which is, by default UNSIGNED. So, basically, you cannot build foreign key from signed field to a table with an unsigned `user_id` field.

Now run the php artisan command on your terminal.


 
	php artisan make:migrate

And then migration worked perfectly with you! If you don't use these(bigInteger and unsigned), you'll get an error like this bellow...


 
Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `web_settings` add constraint `web_settings_user_id_foreign` foreign key (`user_id`) references `users` (`id`))

Hope, you enjoyed it!

Subscribe to newsletter

About Al-Amin Sarker

Profile Picture

Al-Amin Sarker is a Bangladesh times best Web Engineer. He helps to build web based software. He studied at Computer Science and Engineering in Rajshahi University, Rajshahi, Bangladesh. He has more than 6 years of experience on building robust and scalable web applications (not websites) with cutting edge technologies Continue reading...

Do you love my source code ?

Hire me to Develop

Do you want me to
Develop at
Your Software ?
Click here to Hire

facebook box