Reasons and solutions for MySQL failing to create foreign keys

When associating two tables, a foreign key could not be created. From this blog, I saw that the problem was the consistency of the Charset and Collate options in point 6 at the table level and the field level. The encoding charset and collate of my two tables are inconsistent. Both tables execute the SQL statement:

alter table table name convert to character set utf8;

Perfect solution to the problem;

ps: Let's see that MySQL cannot create foreign keys and query foreign key properties

MyISAM and InnoDB Explained

InnoDB and MyISAM are the two most commonly used table types by many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM type tables emphasize performance. Their execution times are faster than InnoDB type, but they do not provide transaction support. InnoDB provides transaction support and advanced database features such as external keys.

Here are some details and implementation differences:

◆1. InnoDB does not support FULLTEXT type indexes.

◆2. InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate the number of rows, but MyISAM simply reads the saved number of rows. Note that when the count(*) statement includes a where condition, the operation is the same for both tables.

◆3. For AUTO_INCREMENT type fields, InnoDB must contain an index that only has this field, but in a MyISAM table, a joint index can be created with other fields.

◆4. When DELETE FROM table, InnoDB does not re-create the table, but deletes rows one by one.

◆5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, then change it back to an InnoDB table after importing the data. However, this does not apply to tables that use additional InnoDB features (such as foreign keys).

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like "%aaa%"

The main difference between the two types is that Innodb supports transactions and foreign key and row-level locks. MyISAM does not support this. Therefore, MyISAM is often considered to be only suitable for use in small projects.

From the perspective of MySQL users, both Innodb and MyISAM are preferred. If the database platform is to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.

Here are the reasons:

1. Most of the projects carried on the platform are read-more and write-less projects, and the read performance of MyISAM is much better than that of Innodb.

2. The index and data of MyISAM are separate, and the index is compressed, so the memory usage rate is greatly improved. It can load more indexes, while Innodb's indexes and data are tightly bundled and no compression is used, which makes Innodb much larger than MyISAM.

3. It often happens that once every 1 or 2 months, application developers accidentally update a table where the range is incorrect, causing the table to become unusable. At this time, the superiority of MyISAM is reflected. Just take out the file of the corresponding table from the compressed package copied that day, put it in a database directory, then dump it into SQL and import it back to the main database, and fill in the corresponding binlog. If it is Innodb, I'm afraid it can't be so fast. Don't tell me to let Innodb regularly use the export xxx.sql mechanism to back up, because the data volume of the smallest database instance is basically tens of GB in size.

4. In terms of application logic, select count(*) and order by are the most frequent operations, accounting for more than 60% of the total SQL statements. Innodb will actually lock the table for this operation. Many people think that Innodb is a row-level lock, which is only valid for the primary key. Non-primary keys will lock the entire table.

5. In addition, there are often many application departments that need me to give them data of certain tables regularly. MyISAM is very convenient. I just need to send them the frm.MYD, MYI files corresponding to the table and let them start the database in the corresponding version. Innodb requires exporting xxx.sql, because if I only give others the file, the other party will not be able to use it due to the influence of the dictionary data file.

6. If compared with MyISAM in insert write operations, Innodb cannot reach the write performance of MyISAM. If it is for index-based update operations, although MyISAM may be inferior to Innodb, it is also a problem whether the slave database can keep up with such high concurrency writes. It is better to solve this problem through a multi-instance sharding architecture.

7. If MyISAM is used, the merge engine can greatly speed up the development of the application department. They only need to perform some select count(*) operations on the merge table. It is very suitable for business tables of a certain type (such as logs, survey statistics) with a total of about hundreds of millions of rows in large projects.

Of course, Innodb is not absolutely not used. For projects that use transactions, Innodb should be used. In addition, some people may say that MyISAM cannot withstand too many write operations, but this can be compensated by the architecture.

SELECT * FROM information_schema.key_column_usage WHERE table_name='table name';
show create table table name;

Summarize

The above is the reason and solution for MySQL's inability to create foreign keys that I introduced to you. I hope it will be helpful to you!