bopvgg
Last Updated: February 25, 2016
·
21.65K
· rizwaniqbal

Creating Foreign Keys via Laravel Migration

I am working on an app on Laravel 4 which requires two tables users and user_profiles. The user_id column in user_profiles is a Foreign Key to id in users. I was trying to do this:

$table->increments('id');
$table->integer('user_id')->unsigned(); // this is meant to be used as a foreign key

However, after doing php artisan migrate it would throw an error:

[Exception]                                                                                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key (SQL: create table `transactions` (`  
  id` int unsigned not null auto_increment primary key, `user_id` int unsigned not null auto_increment primary key) default character set utf8 collate utf8_unicode_ci) (Bindings: array (    
  ))

The problem with Laravel 4 is that, for integer it tries to create an unsigned integer of length (11) while the primary keys are integers of length (10).

To get past this issue, you can use unsignedInteger()

Doing this, will resolve the issue:

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

6 Responses
Add your response

9310
over 1 year ago ·
11051

In the above example, what table is the last bit of code in? It all looks as if it's just referencing itself.

My understanding is it should be:

Schema::create('users',function(Blueprint $table) {
  $table->increments('id');
  $table->unsignedInteger('user_id');
}

Schema::create('user_profiles', function(Blueprint $table) {
  $table->foreign('user_fk_id')->references('id')->on('users');
}

However, this just doesn't make sense as the unsignedInteger('user_id'); is never referenced.

over 1 year ago ·
11052

@adamell :

Say there are two tables user and user_profile . In the user_profile table migration file, you will create a FK for user_id column referencing id on the user table.

The last bit of code is in the migration file for user_profile table

over 1 year ago ·
11060

@rizwaniqbal

Thanks so much for the clarification. I was struggling with this for a few days and it really helped.

over 1 year ago ·
12215

Thanks pal for the tip, was really useful

over 1 year ago ·
16069

This was helpful, thx.

over 1 year ago ·