Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

Laravel - Having trouble adding a foreign key to column

When I run this migration

<?php

    public function up()
    {
        Schema::create('todo_items', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('todo_list_id');
            $table->string('content');
            $table->dateTime('completed_on')->nullable();
            $table->timestamps();
        });

        Schema::table('todo_items', function ($table) {
            $table->foreign('todo_list_id')
                ->references('id')->on('todo_lists')
                ->onDelete('cascade')
                ->onUpdate('cascade');
        });
    }

?>

The table is created, but the addition of a foreign key results in this error (although it says "Can't create table 'odot.#sql-44a_42'", the table is created and accessible after running this migration - I think the error is just for the foreign key..)

SQLSTATE[HY000]: General error: 1005 Can't create table 'odot.#sql-44a_42'
(errno: 150) (SQL: alter table todo_items add constraint todo_items_todo_
list_id_foreign foreign key (todo_list_id) references todo_lists (id)
on delete cascade on update cascade)

I know a preliminary check is to make sure the table creations are running in order. It appears to be okay - tested with the following conditions still results in successful todo_items table creation and foreign key attempt.

if (Schema::hasTable('todo_lists'))
if (Schema::hasColumn('todo_lists', 'id'))
if (Schema::hasColumn('todo_items', 'todo_lists_id'))")

Just in case, here's the migration for creating todo_lists.

<?php

    public function up()
    {
        Schema::create('todo_lists', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name')->unique();
            $table->timestamps();
        }); 
    }

?>

This may well be nothing to do with the migration.. Trying to perform the same action in Sequel Pro:

todo_items(table) >> Relations (top menu) >> Add

Filling out the information still gives

MySQL said: Can't create table 'odot.#sql-44a_2c' (errno: 150)

Chris Shaw
Chris Shaw
26,650 Points

Hi Tom,

Took me a minute or two to work it out but I did, when you create a foreign key the field you're assigning it to needs to be assigned as per Laravel's docs.

In your above schema change the following:

$table->integer('todo_list_id');

to

$table->integer('todo_list_id')->unsigned();

Nice, thanks Chris Upjohn !

1 Answer

Okay, I have a solution... but I don't really understand why.

The bug was coming from an unselected 'unsigned' box on todo_items.todo_list_id field. This box was selected for todo_lists.id and because both rows have to be pretty much identical in datatype etc, this stopped the relationship from being created.

What I should've done is read the warning in the big, massive, bright red box on the Schema Building documentation

Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column >unsigned.

Up at the top of the page, it gives you the information

->unsigned() Set INTEGER to UNSIGNED

But I don't understand what this is - I've never come across it before when creating relationships in phpMyAdmin etc.