Build a many-to-many relationship between two tables: the clean data table (clean_data table) and the user table (user table) established a many-to-many relationship. As shown in the figure below:
1. Create table
. Open navicat and create three tables, clean_data table and user table , and the relationship table between the two tables (clean_data_user table).
The table structures are as follows:
##Related recommendations:《
Navicat for mysql graphic tutorial》
2. Establish foreign keys
2.1 First set the two fields cln_dt_id and user_id to be associated in the clean_data_user table as primary key. As shown in the figure below:
#2.2 Click the foreign key button to establish a foreign key relationship. As you can imagine from the naming of the fields, cln_dt_id will be associated with the id in the clean_data table, and user_id will be associated with the id in the user table. As shown in the figure below:
Notes:
1. When establishing a many-to-many relationship in a database table, you must first establish Primary key. If the foreign key is not established first, an error 1215 will occur, as shown in Figure 6 below. Wasted almost two hours on this issue. It really shouldn’t be.
**
I later discovered that there is no need to create a primary key, just create indexes for cln_td_id and user_id!
**
So, let’s sort out the reasons for the 1215 error:
Error in creating foreign keys in MySQL: 1215 Cannot add the foreign key constraint
1. The types or sizes of the two fields do not strictly match. Among them, the two word shorts are int(6) and int(7), which are not matched. One is unsigned (unsigned), and the other is signed, which is also unmatched.
2. The field trying to set a foreign key is not indexed, or is not a primary key. If one of them is not the primary key, you must first create an index for it.
3. One or two of the tables are tables of the MyISAM engine. If you want to use foreign key constraints, the table must be of the InnoDB engine (actually, if both tables are of the MyISAM engine, this error will not occur at all, but the foreign key will not be generated, only the index 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 related key fields are set to NOTS NULL values. You may solve the problem by modifying the cascade attribute value or setting the field attribute to allow null.
6. Please make sure your Charset and Collate options are consistent at the table level and field level.
7. You may have set a default value for the foreign key, such as default=0.
8. There is a syntax error in the ALTER statement.
The above is the detailed content of How to establish multi-table links in Navicat for MySQL. For more information, please follow other related articles on the PHP Chinese website!
