mysql - 无法在 mySQL 中添加外键

Question

我想像这样添加外键

这是我的表 user_idea

CREATE TABLE IF NOT EXISTS `user_idea` (
      `idea_id` int(5) NOT NULL AUTO_INCREMENT,
      `user_id` varchar(30) DEFAULT NULL,
      `title` varchar(40) DEFAULT NULL,
      `innovators` varchar(30) DEFAULT NULL,
      `idea_categories` varchar(30) DEFAULT NULL,
      `status` int(5) DEFAULT NULL,
      `description` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`idea_id`),
      KEY `innovators` (`innovators`),
      KEY `idea_categories` (`idea_categories`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
    
    --
    -- Dumping data untuk tabel `user_idea`
    --
    
    INSERT INTO `user_idea` (`idea_id`, `user_id`, `title`, `innovators`, `idea_categories`, `status`, `description`) VALUES
    (1, 'jack', 'Video Annotations', 'jack', '1;2', 1, 'Video Annotations Description'),
    (2, 'jack', 'Optimize waterfall model', 'jack;jackson', '3', 0, 'Optimize waterfall model Description'),
    (3, 'jackson', 'Automation', 'jackson', '1', 1, 'Automation Description'),
    (4, 'jackson', 'Design Patterns', 'jackson', '1', 0, 'Design Patterns Description'),
    (5, 'alice', 'Identify Video Objects', 'alice;jack', '2', 1, 'Identify Video Objects Description'),
    (6, 'bob', 'Tin Can LMS', 'bob', '1', 1, 'Tin Can LMS Description'),
    (7, 'bob', 'Text Summarization', 'bob', '2;3', 0, 'Text Summarization Description');
    
    --
    -- Ketidakleluasaan untuk tabel pelimpahan (Dumped Tables)
    --
    
    --
    -- Ketidakleluasaan untuk tabel `user_idea`
    --
    ALTER TABLE `user_idea`
      ADD CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`);
    

我的表idea_category 在此处输入图片描述

 CREATE TABLE IF NOT EXISTS `idea_categories` (
      `category_id` int(5) NOT NULL AUTO_INCREMENT,
      `category_name` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`category_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
        
    INSERT INTO `idea_categories` (`category_id`, `category_name`) VALUES
    (1, 'Project Lifecycle'),
    (2, 'Video'),
    (3, 'Language Analysis');

我的表 user_info： 在此处输入图像描述

CREATE TABLE IF NOT EXISTS `user_info` (
  `id` varchar(30) NOT NULL,
  `full_name` varchar(30) DEFAULT NULL,
  `dep_id` int(5) DEFAULT NULL,
  `point` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dep_id` (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `user_info` (`id`, `full_name`, `dep_id`, `point`) VALUES
('alice', 'Alice W', 2, 1),
('bob', 'Bob S.', 2, 2),
('jack', 'JACK D.', 2, 2),
('jackson', 'M.S.Jackson', 3, 3);

但我得到一个错误无法添加外键？请帮忙

标签： mysql