Mysql Federated Index Usage sample _mysql

This example describes the MySQL federated index. Share to everyone for your reference, specific as follows:

Employee Table UserID
Department Table DeptID
Employee Department Table

Condition: An employee can correspond to multiple departments

Question: How do I set up a database so that it cannot repeatedly add UserID and DeptID?

UUID UserID DeptID
111
212
311 (This is not allowed to add)

DROP TABLE IF EXISTS ' dept ';
CREATE TABLE ' dept ' (
 ' id ' int (one) not null auto_increment,
 ' Deptname ' char (+) 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 (one) not null auto_increment, ' name ' varchar (+) NOT NULL, PRIMARY KEY (' id ')) engin
E=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 () not NULL,
 ' EmployeeID ' int (one) not null,
 ' deptid ' int (one) 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 ');

Remarks: Creates a federated index create index Myindex on employee_dept (Employeeid,deptid);

More information about MySQL interested readers can view the site topics: "MySQL Index operation skills Summary", "MySQL Log Operation skills Encyclopedia", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and " MySQL common function Big Summary "

I hope this article will help you with the MySQL database meter.