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