Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by thomascawthorn
thomascawthorn
22,986 Points

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();
        }); 
    }

?>
thomascawthorn
thomascawthorn
22,986 Points

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,676 Points
Chris Shaw
Chris Shaw
26,676 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();
thomascawthorn
thomascawthorn
22,986 Points

Nice, thanks Chris Upjohn !

1 Answer

thomascawthorn
thomascawthorn
22,986 Points

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.