MySQL与2个表的关系

Question

我有两个表:

CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`industry_id` int(11) DEFAULT NULL,
 ... other fields ...
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
 CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

和:

CREATE TABLE `industries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我尝试向第二个表添加外键:

ALTER TABLE industries ADD FOREIGN KEY (id) REFERENCES companies(industry_id) ON DELETE RESTRICT;

但是我遇到了下一个错误:

Cannot add foreign key constraint

如何添加右 fkey？

Answer 1

我想你做错了。主键不能是外键。让我们做一些小的改变，这可以给你相同的值(value)

ALTER TABLE companies ADD FOREIGN KEY (industry_id) REFERENCES industries(id) ON DELETE RESTRICT;