Shulou(Shulou.com)05/31 Report--

Editor to share with you how to add foreign keys to Navicat, I hope you will gain something after reading this article. Let's discuss it together.

Text

Want to establish a foreign key connection between the class table and the student table, with class as the parent table and student as the child table

Open the design table for student.

If c_id is not allowed to be empty, a Cannot add foreign key constraint prompt will appear when you choose to delete-- > > SET NULL. This is because the deletion is conditional, and the field must be allowed to be empty (if not, foreign keys cannot be created). Of course, if null is not allowed, you can choose to delete-- > > CASCADE can also create foreign keys.

When the box is allowed to be empty

Just save it.

It is also important to note that Engin must be InnoDB.

To sum up:

Foreign key condition

When we use foreign keys, we should follow the following conditions:

For a foreign key to exist, you must first ensure that the engine of the table is InnoDB (the default storage engine). If it is not an InnoDB storage engine, then the foreign key can be created successfully, but there is no constraint.

The field type (column type) of the foreign key field must be exactly the same as the primary key type of the parent table

The names of foreign keys in each table cannot be repeated.

Add the field of foreign key, and if the data already exists, make sure that the data corresponds to the primary key in the parent table.

If the foreign key constraint mode selects SET NULL, then the field must be allowed to be NULL, otherwise Cannot add foreign key constraint appears.

Foreign key constraint

The so-called foreign key constraint refers to the role of foreign key. The role of foreign keys mentioned earlier is the default role, in fact, you can customize the operation through the requirements of foreign keys.

There are three modes of foreign key constraints, which are:

District: strict mode (default). A parent table cannot delete or update a record that has been referenced by child table data.

Cascade: cascading mode, operations of the parent table, and data associated with the corresponding child table are also deleted

Set null: empty the schema. After the operation of the parent table, the data corresponding to the child table (foreign key field) is left empty.

Design the interface of foreign keys, which has seven columns:

Name (name): it can be left empty and will be automatically generated when saved.

Field (FieldName): the foreign key to be set, such as c_id in student

Reference database (Reference DataBase): database associated with foreign keys

Referenced table (Reference Table): associated table, here is the class table

Reference field (Forgin filed Names): associated field, id in class

Delete (ondelete): the action when deleting. I chose SETNULL at that time.

When updating (onupdate): when updating, I chose CASCADE.

The above fields can also be filled in according to your own needs, and you can save them after the settings are completed.

A table already has a record and failed to add a foreign key. In this case, you only need to empty the data in the two tables to be associated and add the foreign key relationship again.

After reading this article, I believe you have a certain understanding of "how to add foreign keys to Navicat". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.