Hello I am using latest Laravel version and this github template https://github.com/thedevdojo/chatter and I am trying to migrate the databases but something seems to go wrong and I cant find what! here are the 3 (I think) migration files needed and the error:
2016_07_29_171128_create_foreign_keys.php
<?php
use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
class CreateForeignKeys extends Migration
{
public function up()
{
Schema::table('chatter_discussion', function (Blueprint $table) {
$table->foreign('chatter_category_id')->references('id')->on('chatter_categories')
->onDelete('cascade')
->onUpdate('cascade');
$table->foreign('user_id')->references('id')->on('users')
->onDelete('cascade')
->onUpdate('cascade');
});
Schema::table('chatter_post', function (Blueprint $table) {
$table->foreign('chatter_discussion_id')->references('id')->on('chatter_discussion')
->onDelete('cascade')
->onUpdate('cascade');
$table->foreign('user_id')->references('id')->on('users')
->onDelete('cascade')
->onUpdate('cascade');
});
}
public function down()
{
Schema::table('chatter_discussion', function (Blueprint $table) {
$table->dropForeign('chatter_discussion_chatter_category_id_foreign');
$table->dropForeign('chatter_discussion_user_id_foreign');
});
Schema::table('chatter_post', function (Blueprint $table) {
$table->dropForeign('chatter_post_chatter_discussion_id_foreign');
$table->dropForeign('chatter_post_user_id_foreign');
});
}
}
2016_07_29_171118_create_chatter_discussion_table.php
<?php
use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
class CreateChatterDiscussionTable extends Migration
{
public function up()
{
Schema::create('chatter_discussion', function (Blueprint $table) {
$table->increments('id');
$table->integer('chatter_category_id')->unsigned()->default('1');
$table->string('title');
$table->integer('user_id')->unsigned();
$table->boolean('sticky')->default(false);
$table->integer('views')->unsigned()->default('0');
$table->boolean('answered')->default(0);
$table->timestamps();
});
}
public function down()
{
Schema::drop('chatter_discussion');
}
}
2016_07_29_171118_create_chatter_categories_table.php
<?php
use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
class CreateChatterCategoriesTable extends Migration
{
public function up()
{
Schema::create('chatter_categories', function (Blueprint $table) {
$table->increments('id');
$table->integer('parent_id')->unsigned()->nullable();
$table->integer('order')->default(1);
$table->string('name');
$table->string('color', 20);
$table->string('slug');
$table->timestamps();
});
}
public function down()
{
Schema::drop('chatter_categories');
}
}
output of php artisan migrate:
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_000000_create_users_table (23.72ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated: 2014_10_12_100000_create_password_resets_table (34.00ms)
Migrating: 2016_07_29_171118_create_chatter_categories_table
Migrated: 2016_07_29_171118_create_chatter_categories_table (18.44ms)
Migrating: 2016_07_29_171118_create_chatter_discussion_table
Migrated: 2016_07_29_171118_create_chatter_discussion_table (27.45ms)
Migrating: 2016_07_29_171118_create_chatter_post_table
Migrated: 2016_07_29_171118_create_chatter_post_table (18.17ms)
Migrating: 2016_07_29_171128_create_foreign_keys
IlluminateDatabaseQueryException
SQLSTATE[HY000]: General error: 1005 Can't create table `testproject`.`chatter_discussion` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `chatter_discussion` add constraint `chatter_discussion_chatter_category_id_foreign` foreign key (`chatter_category_id`) references `chatter_categories` (`id`) on delete cascade on update cascade)
at C:Users*hidden*testtestprojectvendorlaravelframeworksrcIlluminateDatabaseConnection.php:692
688▕ // If an exception occurs when attempting to run a query, we'll format the error
689▕ // message to include the bindings with SQL, which will make this exception a
690▕ // lot more helpful to the developer instead of just the database's errors.
691▕ catch (Exception $e) {
➜ 692▕ throw new QueryException(
693▕ $query, $this->prepareBindings($bindings), $e
694▕ );
695▕ }
696▕ }
1 C:Users*hidden*testtestprojectvendorlaravelframeworksrcIlluminateDatabaseConnection.php:485
PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `testproject`.`chatter_discussion` (errno: 150 "Foreign key constraint is incorrectly formed")")
2 C:Users*hidden*testtestprojectvendorlaravelframeworksrcIlluminateDatabaseConnection.php:485
PDOStatement::execute()
I searched up the problem and as I understood I needed to make the incremented ids unsigned too but it didnt work, same error as before. If I am correct something is wrong with the foreign keys and the create foreign keys but I am not sure what exactly. Thanks in advance,please be kind I am new to Laravel
2
Answers
I think the error is due to your keys during migrations.
Modify the lines that refer to foreign keys like this (in your chatter_discussion / chatter_categories file) :
try to replace
$table->integer('chatter_category_id')->unsigned()->default('1');
by
$table->unsignedBigInteger('chatter_category_id')->default('1');
don’t forget to rollback or delete manuallay tables and records on migrations table on the database.