Navicat for SQL use note (set the default character set, the foreign key cannot be saved)

The following issues were encountered when developing a project with MYSQL+NAVICAT for SQL recently:

1. Default created database, Character set encoding: Latin1--cp1252 West European

Garbled error when storing Chinese in database

2. Use Navicat to add foreign key constraints to the database, save, disappear after saving

Solution:

1 There are 3 ways to solve character set problems, set the field character set, the character set of the table, the data character set

Where set the field character set, the table's character set method is more stupid, need one of the settings, here is briefly described below:

Select the table you want to manipulate in Navicat, select design, place the cursor over the field you want to modify, and modify the character set of the field below.

Above, after selecting "Design", you can see the character set column in the option, and modify the character set of the table.

Finally, modify the default character set of the database, modify the default character set of the database must be completed when the database is created, if the database already exists in the other Character Set table, field, the modification will not take effect

For example, setting a character set when creating a new database

2. There are two reasons why a FOREIGN KEY constraint cannot be saved or lost after saving

1) Foreign_key_checks=0 of the database; All foreign KEY constraints are disabled

To view the values of the current foreign_key_checks, use the following command

SELECT @ @FOREIGN_KEY_CHECKS;

Then use
SET Foreign_key_checks=1;
To start the foreign key constraint.

2)in the MySQL in only InnoDB types of tables support foreign keys and stored procedures 

All fields to create a foreign key must be indexed

Set Table type InnoDB when creating a database

CREATE TABLE ' ROOTTB ' (' ID ' INT (one) UNSIGNED auto_increment not NULL, ' data ' VARCHAR (+) ' NOT null ' DEFAULT ', PRIMARY KEY ( ' ID ')) type=innodb;

In Navicat, when you create a new table, set the engine to InnoDB in the options

Navicat for SQL use note (set the default character set, foreign key not saved)