How to add foreign key in laravel using migration

• Add foreign key in laravel using migration

  --PATH database\migrations\<yourmigrationfile>.php

  $table->foreign('user_id')->references('id')->on('users');
  

  This code snippet states that user_id column references the id column on the users table. This is how you can create a foreign key for your table. 

  [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'user_id' doesn't exist in table. 

  It seems that calling foreign method does not create the column with the reference. This is the most common error found in laravel when you call foreign key method without defining the column in the migration file. So first we have to define the column using $table->integer('user_id'); after that we can use the foreign method to set this column as a foreign key.

• Create column name first and set as foreign key in laravel

  --PATH database\migrations\<yourmigrationfile>.php

  $table->integer('user_id');
  $table->foreign('user_id')->references('id')->on('users');
  

  This code snippet first creates the column user_id with integer data type after that it set this column as a foreign key in the table. You have to use $table->integer('user_id'); before the $table->foreign('user_id')->references('id')->on('users'); in your migration file.  

• Create column name using unsigned method and set as foreign key in laravel

  --PATH database\migrations\<yourmigrationfile>.php

  $table->integer('user_id')->unsigned();
  $table->foreign('user_id')->references('id')->on('users');
  

  This code snippet defines the size and type of the column using the unsigned method. It sets the similar data type for the column of the foreign key (user_id) as the referenced key(id from users table). The size and sign of integer types must be the same.

  [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint.

  For foreign key migrations instead of integer() use unsignedInteger() type or integer()->unsigned(), otherwise you may get SQL errors. In our case we use integer()->unsigned().

  This is the most common error found in laravel when the data type and size for the columns (user_id and id column from users table) are not the same. When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.

• Create column name using index method and adding foreign key constraints with onDelete('cascade') method

  --PATH database\migrations\<yourmigrationfile>.php

  $table->index('user_id');
  $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
  

  In order to get faster results when searching through this particular column we use the index method. Like if you want to search the records using a particular user_id it gives you faster results as compared to other methods.

• Create foreign key with nullable

  --PATH database\migrations\<yourmigrationfile>.php

  $table->unsignedBigInteger('user_id')->nullable();
  $table->foreign('user_id')->references('id')->on('users');
  

  You can set the user_id column value as nullable which sets the value null if you don’t pass any value to it. 

• Create foreign key as nullable and adding foreign key constraints with cascadeOnDelete method

  --PATH database\migrations\<yourmigrationfile>.php

  $table->unsignedBigInteger('user_id')->nullable();
  $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
  

  On delete records from the users table it will also delete the records from this table. Like if you want to delete the user with id 1 this code snippet will delete the records where the foreign key  user_id = 1 in this particular table.

• Create foreign key as nullable and assign null value on delete record using nullOnDelete method

  --PATH database\migrations\<yourmigrationfile>.php

  $table->unsignedBigInteger('user_id')->nullable();
  $table->foreign('user_id')->references('id')->on('users')->nullOnDelete();
  

  On delete record from the user's table, it will set the column value as null for the column user_id in this table for this specific user. Like if you want to delete the user with id 1 this code snippet will set value null where the foreign key user_id = 1 in this particular table.

  This code snippet with the foreign id column is helpful when you have already existing records in the child table and you want to set it as nullable.

Back to code snippet queries related laravel