bopvgg
Last Updated: February 25, 2016
·
19.51K
· rizwaniqbal
605ae30a9f9b68da5ccdd86a5a296f75

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');
Say Thanks
Respond

6 Responses
Add your response

9310
605ae30a9f9b68da5ccdd86a5a296f75
over 1 year ago ·
11051
201939e2d2d7f5e4c874fa0a5a7f9d2d

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
605ae30a9f9b68da5ccdd86a5a296f75

@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
201939e2d2d7f5e4c874fa0a5a7f9d2d

@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
Dmaesjyj

This was helpful, thx.

over 1 year ago ·