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

This article mainly introduces the navicat to establish a foreign key operation, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

The foreign key of the database represents the correlation between the two relationships. A table with the foreign key of another relationship as the primary key is called the master table, and the table with this foreign key is called the slave table of the master table.

1. Open navicat to select the database table to operate, and right-click to select the design table.

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

There are seven columns. Briefly introduce the meaning of these columns:

"name": you can not fill in, you will save the success of the system will be automatically generated.

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

Reference database: the database associated with the foreign key.

Reference Table: associated tabl

Reference Field: associated field

"when deleting": it is the action selected when deleting.

"when updating": it is the action selected when updating.

Extended data: the role of foreign keys

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

If you do not use a foreign key, the student number field of Table 2 has a value (for example, 20140999999), but this value is not in Table 1. At this time, the database allows insertion and does not check the relationship of the inserted data. However, in the case of setting a foreign key, the value you insert into the student number field in Table 2 must be found in the student number field in Table 1. At the same time, if you want to delete a student number field in Table 1, you must ensure that there are no columns in Table 2 that refer to the value of the field, otherwise you cannot delete it. This is called maintaining the consistency and integrity of the data. As shown in the figure on the right, if Table 2 also refers to a student number in Table 1, but you delete the student number in Table 1, Table 2 does not know which student the student number corresponds to.

The tables in the database must conform to the specification in order to put an end to the phenomena of data redundancy, insertion exception, deletion exception and so on. The process of specification 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 the student number 012, 012 in the student table, and 012 in the grade sheet. This consistency is achieved by foreign keys. The function of a foreign key is that 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 grade sheet. The student number in the report card must be the student number in the student form. As a result, the student number in the student form is the same as the student number in the grade sheet. It can be understood intuitively that the function of foreign keys is to achieve the logo consistency of the same thing in different tables. 2 the realization of the function is made up of two tables linked by foreign keys. When operating separately, the foreign key function is realized by two methods [4]:

Prevent execution

● inserts a new row from the table, and its foreign key value is not the primary key value of the primary table.

● modifies the foreign key value from the table. If the new value is not the primary key value of the primary table, the modification is prevented.

Delete rows from the ● master table. If the primary key value exists in the slave table, the deletion is prevented (if you want to delete, you must first delete the relevant rows from the slave table)

The ● master table modifies the primary key value, and the old value exists in the slave table to prevent the modification (if you want to modify, you must first delete the relevant rows of the slave table).

Cascade execution

Delete rows from the ● master table, along with related rows from the table

The ● master table modifies the primary key value, along with the foreign key value of the related rows of the slave table. Two methods are provided for users to choose from. No matter which method you choose, there will be no extra rows from the table. From another point of view, it is consistent with the flag in the master table by rejecting the flag of the same thing in the slave table is inconsistent with the master table.

There are two ways to implement ●. Choose the following ways:

● interface: cascading update and cascading delete two selection boxes, cascade execution is selected, and execution is blocked if not selected.

● command: let E) kSCM) E, RESTRICT two options, CASCADE is cascaded execution, RESTRICT is to block execution.

Thank you for reading this article carefully. I hope the article "how to set up foreign keys in navicat" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.