0

I want to add foreign key like this

enter image description here

and this is my table user_idea

enter image description here

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`);

my table idea_category enter image description here

 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');

My table user_info: enter image description here

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

but I got an error cannot add foreign key? Please help

enter image description here

Barmar
  • 741,623
  • 53
  • 500
  • 612
andreas
  • 47
  • 4
  • "this is my table" Which table is that? There are two tables involved, `user_info` and `user_idea`? We need to see both. and it would be better if you posted plain text in code blocks, not images. – Barmar Aug 27 '20 at 16:21
  • There are hundreds of questions about this error, there can be many reasons for it. Search the existing questions and one of them will match your problem. – Barmar Aug 27 '20 at 16:24
  • I've fixed it my question – andreas Aug 27 '20 at 16:47
  • You have innovators = `jack;jackson`. There is no `user_info` with that `id`. – Barmar Aug 27 '20 at 16:52
  • It doesn't automatically split it and check each name. If you want a many-to-one correspondence, you need to normalize using a relationship table. – Barmar Aug 27 '20 at 16:53
  • You have the same problem with `idea_categories`. You can't use a delimited list as a foreign key. – Barmar Aug 27 '20 at 16:54

0 Answers0