如何在Laravel迁移中连接3个表（语法错误或访问冲突）

Question

I have limited knowledge of Laravel, PHP and MySql. I am trying to set up a database with 3 connected tables, using foreign keys. I did this manually in WorkBench MySql before, but I can't make it in Laravel. Here is my error and my code.

SQL STATE[42000]: Syntax error or access violation: 1072 Key column 'cat_id  
  ' doesn't exist in table (SQL: alter table `users` add index `users_cat_id  
  _usr_id_index`(`cat_id`, `usr_id`)) 

Table 1

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('usr_id')->autoIncrement();
        $table->string('usr_email',45)->unique();
        $table->string('usr_password',256);
        $table->rememberToken();
        $table->string('usr_name',45);
        $table->string('usr_surname',45);
        $table->string('usr_title',45);
        $table->string('usr_psc',5);
        $table->string('usr_region',128);
        $table->string('usr_adress',128);
        $table->string('usr_tel',10);
        $table->string('usr_tel_int',13);
        $table->binary('usr_image');
        $table->tinyInteger('usr_type');
        $table->index(['cat_id','usr_id'])->nullable();
        $table->index(['tem_id','usr_id'])->nullable();

    });
}

Table 2

public function up()
{
     Schema::create('category', function (Blueprint $table) {
         $table->primary('cat_id');
         $table->string('cat_name',45);
     });
     Schema::table('category', function(Blueprint $table) {
         $table->foreign('cat_id')->references('cat_id')->on('users');
      });
 }

Table 3

public function up()
{
     Schema::create('team', function (Blueprint $table) {
       $table->increments('tem_id');
       $table->unique('tem_sub_id');
       $table->string('tem_name',45);
       $table->string('tem_subteam_name',45);

     });
     Schema::table('team', function(Blueprint $table) {
         $table->foreign('tem_id')->references('tem_id')->on('users');
      });

 }

Answer 1

The error occurs because cat_id does not exist in your users table. Apart from that, there are some points to keep in mind when writing Laravel migrations:

• Unless you set FOREIGN_KEY_CHECKS to 0, you can only reference to existing tables, that is why I changed the order of creating your tables in my examples below.

• Keep in mind that, in order to make Laravel magic methods work, you should write your table names in plural form (users, teams, categories).

• Use 'id' for your id's (instead of 'usr_id'). Try to avoid using your table name as a prefix for your database columns.

Because Laravel Eloquent ORM heavily relies on standards, I would suggest to rewrite your tables in a fashion like I did below, following Laravel Eloquent conventions:

Table categories

public function up()
{
     Schema::create('categories', function (Blueprint $table) {
         $table->increments('id');

         $table->string('name',45);
         // etc... 
     });

 }

Table teams

public function up()
{
     Schema::create('teams', function (Blueprint $table) {
       $table->increments('id');

       $table->string('name',45);
       // etc... 

     });
 }

Table users

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('category_id')->unsigned()->nullable();;
        $table->foreign('category_id')->references('id')->on('categories');
        $table->integer('team_id')->unsigned()->nullable(); 
        $table->foreign('team_id')->references('id')->on('teams');

        $table->string('email',45)->unique();
        // etc...

    });
}