Menu
I'm trying to write a laravel database migration but I'm getting the following error about a foreign key:
[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'category_id' doesn't exist in table (SQL: alter table `subcategories` add constraint subcategories_category_id_foreign foreign key (`category_id`) references `categories` (`id`)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'category_id' doesn't exist in table The categories and subcategories tables do get created but the foreign key doesn't. Here's my migration:
<?php use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateCategoryTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('categories', function ($table) { $table->increments('id')->unsigned(); $table->string('name')->unique(); }); Schema::create('subcategories', function ($table) { $table->increments('id')->unsigned(); $table->foreign('category_id')->references('id')->on('categories'); $table->string('name')->unique(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('categories'); Schema::drop('subcategories'); } } Any ideas? Thanks!
947
Firstly you have to make your user_id field an index: $table->index('user_id'); After that you can create a foreign key with an action on cascade: $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
Migrations are optional but recommended. @ecksdee if you don't add a migration which creates a table in the database then you don't need Model in Laravel terms at all, because an Eloquent model is just an object relational mapper to a table that is part of your database.
You should create column before creating a foreign key:
$table->integer('category_id')->unsigned(); $table->foreign('category_id')->references('id')->on('categories'); Documentation: http://laravel.com/docs/5.1/migrations#foreign-key-constraints
156
As Limon Monte mentioned firstly create column then add foreign key constraints
$table->foreignId('category_id'); $table->foreign('category_id')->references('id')->on('categories'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
