mysql 如何正确添加外键约束(错误代码#1215)

Question

总是显示“#1215 - 无法添加外键约束”

这是颜色表

CREATE TABLE `tb_color` (
`color` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
enter code here)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是制造商表

CREATE TABLE `tb_manufacturer` (
`manufacturer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`icon` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是尺码表

CREATE TABLE `tb_size` (
`size` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是状态表

CREATE TABLE `tb_status` (
`status` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是产品表

CREATE TABLE `tb_product` (
`id` int(10) NOT NULL,
`manufacturer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`product` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`size` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`color` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`original_price` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`icon` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) 
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是项目表

CREATE TABLE `tb_item` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`product_id` int(10) NOT NULL,
`price` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`status` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tag1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tag2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tag3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) 
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

这是用户表

CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) 
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

我想添加一些外键。但它总是这样:#1215 - 无法添加外键约束

ALTER TABLE `tb_product` ADD CONSTRAINT `manufacturer` FOREIGN KEY (`manufacturer`) REFERENCES `tb_manufacturer` (`manufacturer`);
ALTER TABLE `tb_product` ADD CONSTRAINT `size` FOREIGN KEY (`size`) REFERENCES `tb_size` (`size`);
ALTER TABLE `tb_product` ADD CONSTRAINT `color` FOREIGN KEY (`color`) REFERENCES `tb_color` (`color`);
ALTER TABLE `tb_item` ADD CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`);
ALTER TABLE `tb_item` ADD CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `tb_product` (`id`);
ALTER TABLE `tb_item` ADD CONSTRAINT `status` FOREIGN KEY (`status`) REFERENCES `tb_status` (`status`);

Answer 1

以下内容现在运行良好。上面有下降的表需要通过添加主键(或至少一个非唯一键)进行调整。否则依赖于它的子表无法查找 FK。我测试过。

-- drop table tb_color;
CREATE TABLE `tb_color` (
`color` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
primary key (color)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

-- drop table tb_manufacturer;
CREATE TABLE `tb_manufacturer` (
`manufacturer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`icon` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
primary key (manufacturer)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

-- drop table tb_size;
CREATE TABLE `tb_size` (
`size` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
primary key (size)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

-- drop table tb_status;
CREATE TABLE `tb_status` (
`status` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
primary key(status)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `tb_product` (
`id` int(10) NOT NULL,
`manufacturer` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`product` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`size` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`color` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`original_price` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`icon` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) 
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `tb_item` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`product_id` int(10) NOT NULL,
`price` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`status` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tag1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tag2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tag3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) 
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) 
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `tb_product` ADD CONSTRAINT `manufacturer` FOREIGN KEY (`manufacturer`) REFERENCES `tb_manufacturer` (`manufacturer`);
ALTER TABLE `tb_product` ADD CONSTRAINT `size` FOREIGN KEY (`size`) REFERENCES `tb_size` (`size`);
ALTER TABLE `tb_product` ADD CONSTRAINT `color` FOREIGN KEY (`color`) REFERENCES `tb_color` (`color`);
ALTER TABLE `tb_item` ADD CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`);
ALTER TABLE `tb_item` ADD CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `tb_product` (`id`);
ALTER TABLE `tb_item` ADD CONSTRAINT `status` FOREIGN KEY (`status`) REFERENCES `tb_status` (`status`);