复合键错误地形成了外键约束 [英] Foreign key constraint is incorrectly formed with Composite Keys

问题描述

我有一张桌子剧院".其中(theater_name，area_name，station)是组合键.在"cubelists"表中，我有要引用的"theaters"表(theater_name，area_name，station)列(thtr_name，area_name，station). /p>

这里的问题是表剧院"中列的组合-(剧院名称，区域名称，工作站)是唯一的，因为它是组合键.但是每列分别不是唯一的.

那我该如何从"cubelists"表中引用这些列?

Schema::create('theaters', function (Blueprint $table) {

        $table->string('theater_name');
        $table->string('area_name');
        $table->string('station');
        $table->primary(array('theater_name','area_name','station'));
        $table->text('address');
        $table->bigInteger('phno');
        $table->string('contact_person');

    });



  public function up()
    {
    //
    Schema::create('cubelists', function (Blueprint $table) {
        $table->string('mvie_name');
        $table->foreign('mvie_name')->references('movie_name')->on('movies');
        $table->string('thtr_name');

        $table->string('area');

        $table->string('stn');

        $table->foreign(array('thtr_name','area','stn'))-
        >references(array('theater_name','area_name','station'))-
        >on('theaters');
        $table->primary(array('mvie_name','thtr_name','area','stn'));
        $table->string('type');
        $table->string('subtype');
        $table->date('validity');
        $table->string('show');

    });

}

如果我提供上面的代码，我会得到一个错误

Migration table created successfully.


  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `boras_cachii`.`#sql-a10_
  112` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter tabl
  e `agreements` add constraint agreements_area_name_foreign foreign key (`area_nam
  e`) references `cubelists` (`area`))



  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `boras_cachii`.`#sql-a10_
  112` (errno: 150 "Foreign key constraint is incorrectly formed")

解决方案

您必须首先创建表，然后创建外键.

Schema::create('cubelists', function (Blueprint $table) {
        $table->string('mvie_name');
        $table->string('area');
        $table->string('stn');
        $table->primary(array('mvie_name','thtr_name','area','stn'));
        $table->string('type');
        $table->string('subtype');
        $table->date('validity');
        $table->string('show');

           $table->foreign(array('thtr_name','area','stn'))
            ->references(array('theater_name','area_name','station'))
             ->on('theaters');

           $table->foreign('mvie_name')
           ->references('movie_name')
           ->on('movies');


    });

theaters表也必须首先迁移，因为cubelists在引用它.并确保外键列和引用列的类型或长度相同.

I have a table "theaters". In which (theater_name,area_name,station) are composite key.And in the table "cubelists" I have columns (thtr_name,area,stn) which are to be referred to (theater_name,area_name,station) of table "theaters".

The problem here is the combination of the columns - (theater_name,area_name,station) in table "theaters" is unique as it is a composite key. But each column separately is not unique.

Then how can I refer these columns from table "cubelists"?

Schema::create('theaters', function (Blueprint $table) {

        $table->string('theater_name');
        $table->string('area_name');
        $table->string('station');
        $table->primary(array('theater_name','area_name','station'));
        $table->text('address');
        $table->bigInteger('phno');
        $table->string('contact_person');

    });



  public function up()
    {
    //
    Schema::create('cubelists', function (Blueprint $table) {
        $table->string('mvie_name');
        $table->foreign('mvie_name')->references('movie_name')->on('movies');
        $table->string('thtr_name');

        $table->string('area');

        $table->string('stn');

        $table->foreign(array('thtr_name','area','stn'))-
        >references(array('theater_name','area_name','station'))-
        >on('theaters');
        $table->primary(array('mvie_name','thtr_name','area','stn'));
        $table->string('type');
        $table->string('subtype');
        $table->date('validity');
        $table->string('show');

    });

}

If I give the above code I get an error as

Migration table created successfully.


  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `boras_cachii`.`#sql-a10_
  112` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter tabl
  e `agreements` add constraint agreements_area_name_foreign foreign key (`area_nam
  e`) references `cubelists` (`area`))



  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `boras_cachii`.`#sql-a10_
  112` (errno: 150 "Foreign key constraint is incorrectly formed")

解决方案

You must first create the table, then foreign keys.

Schema::create('cubelists', function (Blueprint $table) {
        $table->string('mvie_name');
        $table->string('area');
        $table->string('stn');
        $table->primary(array('mvie_name','thtr_name','area','stn'));
        $table->string('type');
        $table->string('subtype');
        $table->date('validity');
        $table->string('show');

           $table->foreign(array('thtr_name','area','stn'))
            ->references(array('theater_name','area_name','station'))
             ->on('theaters');

           $table->foreign('mvie_name')
           ->references('movie_name')
           ->on('movies');


    });

Also theaters table must migrate first since cubelists is referencing it. And make sure that the foreign key column and the referencing column are the same type or length.

这篇关于复合键错误地形成了外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！