Shulou(Shulou.com)06/01 Report--

This article will explain in detail the operation steps of establishing multi-table links in Navicat for MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Build a many-to-many relationship between two tables: clean the data table (clean_data table) and the user table (user table) to establish a many-to-many relationship. As shown in the following figure:

1. Create a tabl

. Open navicat and create three tables, the clean_ data table and the user table, and the relationship table between the two tables (the clean_data_user table).

The structure of the table is as follows:

two。 Establish a foreign key

2.1First, set the cln_dt_id and user_id fields to be associated in the clean_data_user table as the primary key. As shown in the following figure:

2.2 Click the foreign key button to establish a foreign key relationship. As you can imagine from the name of the field, cln_dt_id will be associated with id in the clean_ data table, and user_id will be associated with id in the user table. As shown in the following figure:

Note:

1. When establishing a many-to-many relationship with a database table, you must first establish a primary key. If you do not create a foreign key first, an error of 1215 will occur, as shown in figure 6 below. Nearly two hours were wasted on this issue. I really shouldn't have.

**

Later, I found that there is no need to build a primary key, just index cln_td_id and user_id!

**

So, let's sort out the cause of the 1215 error:

Error creating foreign key in MySQL: 1215 Cannot add the foreign key constraint

1. The type or size of the two fields do not strictly match. Among them, the short words int (6) and int (7) are also mismatched. One is unsigned (unsigned), and the other is signed is also mismatched.

two。 The field trying to set the foreign key is not indexed or is not a primary key (primary key). If one of them is not primary key, you must first create an index for it.

3. One or two of these tables are MyISAM engine tables. To use foreign key constraints, the table must be an InnoDB engine (in fact, if both tables are MyISAM engine, this error will not occur at all, but foreign keys will not be generated, only indexes will be created) you need to check the engine type of the table.

4. The name of the foreign key cannot be repeated.

5. You may have set ON DELETE SET NULL, but the field of the related key is set to the NOTS null value. You may solve this by changing the value of the cascade property or setting the field property to allow null.

6. Please make sure that your Charset and Collate options are consistent at the table level and field level.

7. You may have set a default value for foreign keys, such as default=0.

There is a syntax error in the 8.ALTER declaration.

On the "Navicat for MySQL to establish multi-table link procedures" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.