mysql与2表的关系

sauutmhj  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(297)

我有两张table:

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;

但下一个错误是:
无法添加外键约束
如何添加正确的fkey?

cmssoen2

cmssoen21#

我想你做错了。主键不能是外键。让我们做一些小的改变,它可以给你同样的价值

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

相关问题