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 keyHowever, 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');Written by Rizwan Iqbal
Related protips
6 Responses
Look at https://github.com/laravel/laravel/issues/2212 for more information
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.
@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
Thanks so much for the clarification. I was struggling with this for a few days and it really helped.
Thanks pal for the tip, was really useful
This was helpful, thx.
