本文實例講述了MySQL聯合索引。分享給大家供大家參考，具體如下：
員工表 userid
部門表 deptid
員工部門表
條件：一個員工可以對應多個部門
問題：怎么樣設置數據庫，讓其不能重復添加 userid 和deptid？
uuid userid deptid
111
212
311(這個就不能讓其添加)
DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptname` char(32) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ------------------------------ Records of dept-- ----------------------------INSERT INTO `dept` VALUES ('1', '1');INSERT INTO `dept` VALUES ('2', '2');DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ------------------------------ Records of employee-- ----------------------------INSERT INTO `employee` VALUES ('1', '11');DROP TABLE IF EXISTS `employee_dept`;CREATE TABLE `employee_dept` ( `id` int(11) NOT NULL, `employeeid` int(11) NOT NULL, `deptid` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `bb` (`deptid`), KEY `myindex` (`employeeid`,`deptid`), CONSTRAINT `aa` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`id`), CONSTRAINT `bb` FOREIGN KEY (`deptid`) REFERENCES `dept` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of employee_dept-- ----------------------------INSERT INTO `employee_dept` VALUES ('1', '1', '1');INSERT INTO `employee_dept` VALUES ('2', '1', '2');備注：創建聯合索引create index myindex on employee_dept (employeeid,deptid);
希望本文所述對大家MySQL數據庫計有所幫助。
新聞熱點
疑難解答
