它总是显示“#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`);

最佳答案

现在,以下各项运行正常。需要对其进行下拉的表需要添加主键(或至少是非唯一键)进行调整。否则,取决于它的子表无法为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`);

09-11 00:24
查看更多