dqcwl02022
2013-08-24 21:08 阅读 83
已采纳

MySQL错误代码1005,SQL状态HY000:无法创建表'cpis.cpis_sudent_profile'(错误号:150)

I need your assistance conditional! I could not determine what the problem is... I think that everything is correct, but MySQL throws an error 1005, SQL state HY000: Can't create table 'cpis.cpis_sudent_profile' (errno: 150)

DROP TABLE IF EXISTS `cpis_users`;
CREATE TABLE `cpis_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `activkey` varchar(128) NOT NULL DEFAULT '',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastvisit_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` boolean NOT NULL DEFAULT false,
  `user_type` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `cpis_countries`;
CREATE TABLE `cpis_countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(3) NOT NULL,
  `title_ru` varchar(100) DEFAULT NULL,
  `title_en` varchar(100) DEFAULT NULL,
  `title_cz` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `cpis_programs`;
CREATE TABLE `cpis_programs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title_ru` varchar(100) DEFAULT NULL,
  `description_ru` text DEFAULT NULL,
  `title_en` varchar(100) DEFAULT NULL,
  `description_en` text DEFAULT NULL,
  `title_cz` varchar(100) DEFAULT NULL,
  `description_cz` text DEFAULT NULL,
  `publicated` boolean DEFAULT false,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `cpis_sudent_profile`;
CREATE TABLE `cpis_sudent_profile` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `fname` varchar(100) NOT NULL,
  `pname` varchar(100) NOT NULL,
  `lname` varchar(100) NOT NULL,
  `birthday` date NOT NULL,
  `citizenship` int(11) NOT NULL,
  `sex` varchar(30) NOT NULL,
  `program_id` varchar(100) NOT NULL,
  `owner` int(11) DEFAULT NULL,
  `member` int(11) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   CONSTRAINT `spfk_1` FOREIGN KEY (`user_id`) REFERENCES `cpis_users` (`id`),
   CONSTRAINT `spfk_2` FOREIGN KEY (`citizenship`) REFERENCES `cpis_countries` (`id`),
   CONSTRAINT `spfk_3` FOREIGN KEY (`program_id`) REFERENCES `cpis_programs` (`id`),
   CONSTRAINT `spfk_4` FOREIGN KEY (`owner`) REFERENCES `cpis_users` (`id`),
   CONSTRAINT `spfk_5` FOREIGN KEY (`member`) REFERENCES `cpis_users` (`id`)  
) ENGINE=InnoDB CHARSET=utf8;
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doxrxwn2252 doxrxwn2252 2013-08-24 21:17

    program_id varchar(100) should be of the same data type as cpis_programs (id int(11)). By having different types of data, failure to establish the constraint. Example SQL Fiddle.

    ...
    DROP TABLE IF EXISTS `cpis_sudent_profile`;
    
    CREATE TABLE `cpis_sudent_profile` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL,
      `fname` varchar(100) NOT NULL,
      `pname` varchar(100) NOT NULL,
      `lname` varchar(100) NOT NULL,
      `birthday` date NOT NULL,
      `citizenship` int(11) NOT NULL,
      `sex` varchar(30) NOT NULL,
      /*`program_id` varchar(100) NOT NULL,*/
      `program_id` int(11) NOT NULL,
      `owner` int(11) DEFAULT NULL,
      `member` int(11) DEFAULT NULL,
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
       CONSTRAINT `spfk_1` FOREIGN KEY (`user_id`) REFERENCES `cpis_users` (`id`),
       CONSTRAINT `spfk_2` FOREIGN KEY (`citizenship`) REFERENCES `cpis_countries` (`id`),
       CONSTRAINT `spfk_3` FOREIGN KEY (`program_id`) REFERENCES `cpis_programs` (`id`),
       CONSTRAINT `spfk_4` FOREIGN KEY (`owner`) REFERENCES `cpis_users` (`id`),
       CONSTRAINT `spfk_5` FOREIGN KEY (`member`) REFERENCES `cpis_users` (`id`)  
    ) ENGINE=InnoDB CHARSET=utf8;
    
    点赞 评论 复制链接分享

相关推荐