Reasons and Solutions of MySQL Failing to Create Foreign Key

When associating two tables, a foreign key cannot be created. From this blog, the problem lies in the uniformity of Charset and Collate options in point 6 at table level and field level. My two tables have different codes charset and collate, and both tables execute SQL statements:

alter table  Table name  convert to character set utf8;

Solve the problem perfectly;

ps: Let's take a look at MySQL. It can't create a foreign key. Query the properties of the foreign key

Explanation of MyISAM and InnoDB

InnoDB and MyISAM are the two most commonly used table types when many people use MySQL, and each has its 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, whereas the InnoDB type does. Tables of type MyISAM emphasize performance and execute several times faster than those of type InnoDB, but do not provide transactional support, whereas InnoDB provides transactional support and advanced database functionality such as foreign keys.

Here are some details and implementation differences:

1. InnoDB does not support indexes of type FULLTEXT.

2. The specific number of rows in the table is not saved in InnoDB, that is to say, when select count (*) from table is executed, InnoDB scans the whole table once to calculate how many rows there are, but MyISAM simply reads out the saved number of rows. Note that when the count (*) statement contains the where condition, the two tables operate in the same way.

3. For fields of type AUTO_INCREMENT, InnoDB must contain an index only for that field, but in the MyISAM table, joint indexes can be established with other fields.

4. When DELETE FROM table, InnoDB does not re-establish the table, but deletes one row at a time.

5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to change the InnoDB table into MyISAM table first, and then change it into InnoDB table after importing data, but it is not applicable to tables using additional InnoDB features (such as foreign keys).

In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing an SQL statement, InnoDB table will also lock the whole 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. But MyISAM does not support, so MyISAM is often easy to be considered only suitable for small projects.

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

The reasons are as follows:

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

2. The index and data of MyISAM are separated, and the index is compressed, so the memory utilization rate is correspondingly improved. More indexes can be loaded, while Innodb is tightly bundled with indexes and data, and no compression is used, which makes Innodb larger than MyISAM.

3. It often happens every 1 or 2 months that application developers accidentally write the wrong range of update1 table where, which leads to the table not being used normally. At this time, the superiority of MyISAM is reflected. Take out the files corresponding to the table from the compressed package copied on the same day and put them in a database directory casually. Then dump becomes sql and then imports them back to the main library, and make up the corresponding binlog. If it is Innodb, I'm afraid it can't be so fast. Don't tell me to let Innodb backup regularly with the mechanism of exporting xxx. sql, because the data volume of the smallest database instance is basically 10G.

4. In terms of contact application logic, select count (*) and order by are the most frequent operations, which can account for more than 60% of the total statements of sql, and this operation Innodb actually locks tables. Many people think that Innodb is a row-level lock, but where is effective for its primary key, and non-primary keys will lock the whole table.

5. There are often many application departments that need me to give them some table data regularly. MyISAM is very convenient. Just send them the files of frm. MYD and MYI corresponding to that table, and let them start in the corresponding version of the database. Innodb needs to export xxx. sql, because only giving others files is affected by dictionary data files, and the other party cannot use it.

6. If MyISAM is better than insert, Innodb can't achieve the writing performance of MyISAM. If it is for index-based update, although MyISAM may be inferior to Innodb, it is also a problem whether the library can catch up with it, so it is better to solve it through multi-instance sub-library and sub-table architecture.

7. If MyISAM is used, merge engine can greatly speed up the development of application departments. They only need to do some select count (*) operations on this merge table, which is very suitable for a certain type of rows business table with a total amount of several hundred million projects (such as logs, investigation statistics).

Of course, Innodb is not absolutely unnecessary, and Innodb is used for projects with transactions. In addition, some people may say that your MyISAM can't resist too many writes, but it can be compensated by architecture.

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

Summarize

The above is the site to introduce to you MySQL can not create foreign key reasons and solutions, I hope to help you!