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

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;
    
    点赞 评论

相关推荐 更多相似问题