Examples of using MySQL combined index and mysql Index

Examples of using MySQL combined index and mysql Index

This example describes the MySQL joint index. We will share this with you for your reference. The details are as follows:

Employee table userid
Department table deptid
Employee Department table

Condition: An employee can correspond to multiple departments.

Q: How to set up a database so that it cannot add userid and deptid repeatedly?

Uuid userid deptid
111
212
311 (this cannot be added)

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');

Note: create index myindex on employee_dept (employeeid, deptid );