php - Laravel 在迁移文件上添加外键

Question

我在card_price上有这个结构数据库中的表:

CREATE TABLE IF NOT EXISTS `card_price` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card_price` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` tinyint(3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

这个结构为users :

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `family` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

我正在使用 migration 创建此表文件例如:

card_price :

Schema::create('card_price',function(Blueprint $table){
    $table->increments('id');
    $table->string('card_price');
    $table->tinyInteger('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on('users');
    $table->timestamps();
});

和users :

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name','20');
    $table->string('family','25');
    $table->string('username','15');
    $table->string('password','64');
    $table->string('email','20');
    $table->string('remember_token','100');
    $table->timestamps();
});

使用此迁移文件，我尝试使用 user_id 创建外键上card_price表和id上users table 。不幸的是我收到这个错误:

  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL
  : alter table `card_price` add constraint card_price_user_id_foreign foreig
  n key (`user_id`) references `users` (`id`))

  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Answer 1

由于外部表中的数据类型不匹配，您收到此错误。您应该对引用它的父表和子表列使用相同的数据类型。

将card_price表中user_id的数据类型更改为INT UNSIGNED NOT NULL；

首先使用以下查询更改您的表:

ALTER TABLE `card_price` 
CHANGE COLUMN `user_id` `user_id` INT UNSIGNED NOT NULL ;

外键引用查询:

ALTER TABLE `card_price` 
ADD CONSTRAINT `fk_users`
  FOREIGN KEY (`user_id`)
  REFERENCES `users` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;