Laravel 5 与外键相关的迁移错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28571818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel 5 Migration Error Related to Foreign Key
提问by mtpultz
This is an attempt to learn Laravel (specifically v5.x) by porting an old project using a SQL dump that contains this:
这是通过使用包含以下内容的 SQL 转储移植旧项目来学习 Laravel（特别是 v5.x）的尝试：
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;
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:
来自几个 stackoverflow 问题1, 2, 3；我已经更改了我的原始代码，将 Schema::table 的外键从 Schema::create 中分离出来，并将 unsigned() 添加到每个字段中：
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))
Which according to MySQLmeans:
根据MySQL，这意味着：
Error: 1215 SQLSTATE: HY000 (ER_CANNOT_ADD_FOREIGN)
Message: Cannot add foreign key constraint
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.
我确信在修复此问题后，它会出现在所有其他表中，这些表相似或具有多个外键，因此希望任何修复方法都适用于其余表。
回答by Xavi Martínez
You have to change the migrations order
您必须更改迁移顺序
|-database/
|--migrations/
|---2015_02_02_141725_create_users_table.php
|---2015_03_01_234626_create_locations_table.php
Should be:
应该：
|-database/
|--migrations/
|---2015_01_01_234626_create_locations_table.php
|---2015_02_02_141725_create_users_table.php
Finally:
最后：
php artisan migrate
回答by dschniepp
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.
该错误是由尝试在不存在的列上引用的迁移引起的。为防止出现此问题，请在执行外键创建之前创建引用列。
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:migrationcommand.
要创建具有依赖关系的表结构，您可以按正确的顺序创建迁移文件，或者将所有迁移放入一个大文件中，或者至少创建没有外键的所有表，然后创建另一个文件来执行最后的键创建。请记住，这仅在您想要重新编写现有数据结构或想要修复迁移顺序时才需要。否则使用php artisan make:migration命令。
回答by Claudio Oyarzùn
I had the same problem, what did I do?
我遇到了同样的问题，我是怎么做的？
simple... in your create_usersmigration file try this....
简单......在你的create_users迁移文件中试试这个......
public function up()
{
Schema::create('location', function(Blueprint $table)
{
$table->increments('id');
...other attributes...
$table->rememberToken();
$table->timestamps();
});
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->integer('locationid')->unsigned();
$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');
});}
:)
:)
回答by Safoor Safdar
Well, its little confusing part here, first create an table('users') when up method run and then respectively update table('users') by adding in Foreign key in the same table. I think its can not perform at the same time add and update using migration. For the solution of your code would be:
好吧，这里有点令人困惑，首先在 up 方法运行时创建一个表（'users'），然后通过在同一个表中添加外键来分别更新表（'users'）。我认为它不能同时使用迁移执行添加和更新。对于您的代码的解决方案是：
public function up()
{
Schema::create('users', function(Blueprint $table)
{
//...
$table->integer('locationid')->unsigned();
$table->foreign('locationid')->references('id')->on('location');
//...
});
}
Now, you creating your foreign by creating your table. so its should work if not let me know.
现在，您通过创建您的表来创建您的外来语。所以如果不让我知道它应该可以工作。
回答by Emmanuel Benson
Make sure your location table exists first. Referencing a non existing table can cause the error you're having.
首先确保您的位置表存在。引用不存在的表可能会导致您遇到错误。
