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 trialthomascawthorn
22,986 PointsLaravel - 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 tabletodo_items
add constraint todo_items_todo_
list_id_foreign foreign key (todo_list_id
) referencestodo_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();
});
}
?>
Chris Shaw
26,676 PointsHi 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
22,986 PointsNice, thanks Chris Upjohn !
1 Answer
thomascawthorn
22,986 PointsOkay, 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.
thomascawthorn
22,986 Pointsthomascawthorn
22,986 PointsThis 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)