dpdhnd3577 2015-11-08 19:57
浏览 160
已采纳

Laravel在迁移文件上添加外键

i have this structure on card_price table in database:

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 ;

and this structure for 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 ;

i'm creating this tables with migration files such as:

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();
});

and 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();
});

with this migration files i'm trying to create foreign key with user_id on card_price table and id on users table. unfortunately i get this error:

  [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
  • 写回答

1条回答 默认 最新

  • doushaqing7080 2015-11-08 20:05
    关注

    You are getting this error because of data type mismatch in foreign table. You should use same data type for parent and child table table column referencing it.

    Change data type of user_id in card_price table to INT UNSIGNED NOT NULL;

    First Alter your table with following query:

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

    query for foreign key reference:

    ALTER TABLE `card_price` 
    ADD CONSTRAINT `fk_users`
      FOREIGN KEY (`user_id`)
      REFERENCES `users` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥66 定制开发肯德基自动化网站下单软件
  • ¥20 vscode虚拟环境依赖包未安装
  • ¥15 odoo17关于owl开发js代码问题
  • ¥15 光纤中多普勒频移公式的推导
  • ¥15 怎么制作一个人脸识别门禁系统
  • ¥20 大华dss监控平台网络关闭登不进去
  • ¥15 请使用蚁群算法解决下列问题,并给出我完整的代码
  • ¥20 关于php录入完成后,批量更新数据库
  • ¥15 请教往复密封润滑问题
  • ¥15 cocos creator发布ios包