In-depth parsing MySQL Association Table Creation

Creating an association table in MySQL makes our database more accurate and practical. When we mention creating an association table in MySQL, we cannot help but mention the concept of integrity.

Referentialintegrity is an important concept in database design. In different lists of systems, the integrity of the reference is involved when all references of the database are legal or illegal. When the integrity of the reference exists, any association with the non-existent reCord becomes invalid, which prevents various errors and provides a more accurate and practical database.

Integrity of reference is usually widely used through the use of foreign keys. For a long time, the popular open-source RDBMSMySQL tool does not support foreign keys because such support will reduce the speed and performance of RDBMS. However, many users are interested in the advantages of the integrity of the reference. Recently, different MySQL versions support foreign keys through the new InnoDB list engine. Therefore, it is very easy to maintain the integrity of the reference in the list of databases.

To establish a foreign key relationship between two MySQL tables, MySQL must meet the following three conditions:

* The two tables must be InnoDB tables.
* The fields using the foreign key relationship must be indexed ).
* The fields using the foreign key relationship must be of the same data type.

The example is the best way to understand the above points. Create a parts table, use the cpu field to store all the cpu accessory models, and then create a new pc table, the cpumodel field is used to store the cpu model in the PC. Obviously, all records in the cpumodel field should exist in the parts table.

 
 

  
  
mysql> create table parts(   
  
  
-> cpu char(20) not null,   
  
  
-> index(cpu)   
  
  
-> )engine=innodb;   
  
  
Query OK, 0 rows affected (0.01 sec)   
  
  
 
  
  
mysql> create table pc(   
  
  
-> cpumodel char(20) not null,   
  
  
-> index(cpumodel),   
  
  
-> foreign key(cpumodel) references parts(cpu)   
  
  
-> )engine=innodb;   
  
  
Query OK, 0 rows affected (0.01 sec)   
  
  
 
 
 

Note: For non-InnoDB tables, the foreign key statement is ignored. Add data 1, 2, 3 to the parts table, and then test the pc table. If 1 meets the conditions, the data can be smoothly inserted. If the character 5 does not meet the conditions, A foreign Key Binding error occurs. This is what we want.

 
 

  
  
mysql> insert into parts values('1'),('2'),('3');   
  
  
Query OK, 3 rows affected (0.00 sec)   
  
  
Records: 3 Duplicates: 0 Warnings: 0   
  
  
 
  
  
mysql> insert into pc values('1');   
  
  
Query OK, 1 row affected (0.01 sec)   
  
  
 
  
  
mysql> insert into pc values('5');   
  
  
ERROR 1452 (23000): Cannot add or update a child row: a foreign   
  
  
key constraint fails (`Orange/pc`, CONSTRAINT `pc_ibfk_1` FOREIGN KEY   
  
  
(`cpumodel`) REFERENCES `parts` (`cpu`))   
  
  
 
 
 

As explained in the preceding example, foreign keys play an important role in identifying data entry errors, so that a more robust and integrated database can be created. On the other hand, it is worth mentioning that executing foreign key verification is a process of internal data processing, and specifying complex internal relationships between different tables can lead to database performance degradation. Therefore, it is important to find a balance between the integrity of the reference and performance considerations. The use of foreign keys ensures the optimal combination between performance and stability.

Alternative usage of MySQL table alias

Solution to three types of mysql tabulation garbled data

How to modify the table structure in mysql

Three Common MySQL table creation statements

MySQL connection Query