Laravel 5与外键相关的迁移错误 [英] Laravel 5 Migration Error Related to Foreign Key

问题描述

这是尝试通过使用包含以下内容的SQL转储移植旧项目来学习Laravel（特别是v5.x）：

This is an attempt to learn Laravel (specifically v5.x) by porting an old project using a SQL dump that contains this:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `locationid` int(11) NOT NULL,
  `username` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `email` varchar(200) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `active` bit(1) DEFAULT b'1',
  `token` varchar(100) DEFAULT NULL,
  `token_created` timestamp NULL DEFAULT NULL,
  `role` varchar(45) NOT NULL DEFAULT 'user',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  UNIQUE KEY `username_UNIQUE` (`username`),
  KEY `location_idx` (`locationid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

在转储的底部有这个特定的表格：

And at the bottom of the dump had this related to this specific table:

ALTER TABLE `user`
  ADD CONSTRAINT `location_userfk` FOREIGN KEY (`locationid`) REFERENCES `location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

从一对stackoverflow问题 1 ， 2 ， 3 ;我改变了我的原始代码，从Schema :: create中分割出外键到Schema :: table，并在每个字段中添加了unsigned（）：

From a couple stackoverflow questions 1, 2, 3; I've altered my original code to split out the foreign key to Schema::table from Schema::create and added unsigned() to each of the fields:

public function up()
{
    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('locationid')->unsigned();
        // $table->foreign('locationid')->references('id')->on('location');
        $table->string('username', 60)->unique();
        $table->string('email', 200)->unique();
        $table->string('firstname', 100);
        $table->string('lastname', 100);
        $table->string('password', 255);
        $table->boolean('active')->default(TRUE);
        $table->string('role', 45)->default('user');
        // $table->string('token', 255)->nullable()->default(NULL);
        // $table->string('token_create')->nullable()->default(NULL);
        $table->rememberToken();
        $table->timestamps();
    });

    Schema::table('users', function(Blueprint $table) 
    {
        $table->foreign('locationid')->references('id')->on('location');
    });
}

但是，当我迁移时， ：

But I still get an error when I migrate when using an empty newly created DB:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint' in /home/vagrant/projects/communityfuturesbc/vendor/laravel/framework/src/Illuminate/Database/Connection.php:358
Stack trace:
#0 /home/vagrant/projects/communityfuturesbc/vendor/laravel/framework/src/Illuminate/Database/Connection.php(358): PDOStatement->execute(Array) etc...

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[HY000]: General err
or: 1215 Cannot add foreign key constraint (SQL: alter table `users` add constraint users_loca
tionid_foreign foreign key (`locationid`) references `location` (`id`))' in /home/vagrant/proj
ects/communityfuturesbc/vendor/laravel/framework/src/Illuminate/Database/Connection.php:614   
Stack trace:                                                                                  
#0 /home/vagrant/projects/communityfuturesbc/vendor/laravel/framework/src/Illuminate/Database/Connection.php(570): Illuminate\Database\Connection->runQueryCallback('alter table `us...', Ar
ray, Object(Closure))

根据 MySQL 表示：

Error: 1215 SQLSTATE: HY000 (ER_CANNOT_ADD_FOREIGN)
Message: Cannot add foreign key constraint                                         

之后，我无法回滚或再迁移，而不会在DB中存在users表的另一种类型的错误，所以每次我尝试一个版本的上述代码我

After which I can't rollback or migrate again without getting another type of error regarding the existence of users table already in the DB so each time I try a version of the above code I've been dropping my DB each time.

我确信在修正这个问题后，它会出现在所有其他表，这是类似或有多个外键，所以希望

I'm sure after fixing this it will occur in all the other tables, which are similar or have more than one foreign key so hopefully whatever fixes this will work for the rest.

推荐答案

错误是由尝试在非现有列上引用的迁移引起的。要避免此问题，请在执行外键创建之前创建引用列。

The error is caused by the migration which tries to reference on an non existing column. To prevent this issue create referenced column before performing the foreign key creation.

要创建具有依赖关系的表结构，您可以按正确的顺序创建迁移文件，或将所有迁移到一个大文件或至少创建所有没有外键的表，并创建另一个文件，最后执行键创建。保持在最小这只是必要的，如果你想要解释一个现有的数据结构或要修复迁移顺序。否则使用 php artisan make：migration 命令。

To create table structures with dependencies you can either create the migration files in the correct order, or put all migrations into one big file or at least create all tables without foreign keys and create another file which performs the key-creation in the end. Keep in min this is only necessary if you want to rescript an existing data structure or want to fix the migration order. Otherwise use the php artisan make:migration command.

这篇关于Laravel 5与外键相关的迁移错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！