How to establish relationships between tables in navicat

Navicat is a set of fast, reliable and relatively cheap database management tools, designed to simplify database management and reduce system management costs. It is designed to meet the needs of database administrators, developers and small and medium-sized businesses. Below we will introduce to you how navicat establishes relationships with tables.

Recommended tutorial: navicat graphic tutorial

##The relationship between tables in the database is reflected by a foreign key . So to establish a relationship between two tables, you actually create a foreign key to the table.

1. Open navicat, select the database table to be operated, right-click and select Design Table.

#2. Click the foreign key option to start setting foreign keys.

There are seven columns in total. Let’s briefly introduce the meaning of these columns:

“Name”: You don’t need to fill it in. The system will automatically generate it after you save it successfully.

"Field": It means which key you want to set as a foreign key.

"Reference database": Database associated with foreign keys.

"Reference table": associated table

"Reference field": associated field,

"When deleting": It is the action selected when deleting.

"When updating": It is the action selected when updating.

Extended information: The role of foreign keys

　

Maintain data consistency and integrity, the main purpose is to control the storage in foreign key tables The data in . To associate two tables, the foreign key can only refer to the values ​​of columns in the table or use null values.

If no foreign key is used, a value (such as 20140999999) is inserted into the student number field of Table 2, but this value does not exist in Table 1. At this time, the database allows the insertion and will not block the inserted value. Data is checked for relationships. However, when setting up a foreign key, the value you insert into the student ID field of Table 2 must be found in the student ID field of Table 1. At the same time, if you want to delete a certain student number field in Table 1, you must ensure that there is no column in Table 2 that references the field value, otherwise it will not be deleted. This is called maintaining data consistency and integrity. As shown on the right, if Table 2 still references a certain student ID in Table 1, but you delete the student ID in Table 1, Table 2 will not know which student the student ID corresponds to.

The tables in the database must comply with the specifications to prevent data redundancy, insertion anomalies, deletion anomalies and other phenomena. The canonical process is the process of decomposing the table. After decomposition, the representative attributes of a thing appear in different tables. Obviously, they should be consistent. For example, the representative data of a student is student number 012, which is 012 in the student table and should also be 012 in the grade table. This consistency is achieved by foreign keys. The function of a foreign key is: its value must be the primary key value of another table. The student number is the primary key in the student table and the foreign key in the grades table. The student number in the grade list must be the student number in the student list. Therefore, the student number in the student table and the student number in the grade table are consistent. It can be intuitively understood that the function of foreign keys is to achieve consistency of signs of the same thing in different tables. 2 Functional realization of two tables connected by foreign keys. When operated separately, the foreign key function is realized by two methods [4]: ​​

Prevent execution

　●Insert a new row from the table, and the foreign key value is not the primary key value of the main table, which prevents the insertion;

　●Modify the foreign key value from the table, and the new value is not the primary key of the main table The value will prevent modification;

●If a row is deleted from the master table, its primary key value will be prevented from being deleted if it exists in the slave table (if you want to delete it, you must first delete the relevant rows from the slave table);

●The master table modifies the primary key value. If the old value exists in the slave table, the modification is blocked (if you want to modify it, you must first delete the relevant rows from the slave table).

Cascade execution

●Delete rows from the main table, and delete related rows from the table together;

●Modify the primary key value from the main table, Modify the foreign key values ​​of related rows from the table together. Two methods are provided for users to choose. No matter which method is chosen, there will be no extra rows from the table. From another perspective, the same thing is achieved by rejecting the inconsistency between the flags in the slave table and the master table to achieve consistency with the flags in the master table.

　●Two implementation methods, choose through the following methods:

●Interface: Set up two selection boxes for cascade update and cascade delete. If selected, the cascade will be executed, and if not selected, it will be blocked. Execution;

●Command: Let E)kSCM)E and RESTRICT be two optional options, CASCADE means cascade execution, and RESTRICT means prevent execution.

The above is the detailed content of How to establish relationships between tables in navicat. For more information, please follow other related articles on the PHP Chinese website!