MySQL Add foreign KEY constraint

Simply say the benefits of using foreign keys

1. Integrity constraints

Like what:
User table with field user number (ID), name (username)
Device table with field device number (ID), device name (devicename) device belongs to user number (USER_ID)

Set the user number in the device table to a foreign key, referencing the primary key of the user table.
When entering data into a device table, if the input user number cannot be found in the user's table, an error will be made to ensure that the device must belong to an existing user.

2. Cascade Delete can be implemented

Also refer to the above example, when a user is not using the system, you need to remove the user and his device from the database, if you do not use foreign keys, you need to delete the data from the user table and the device table, if you use foreign keys, only delete the user, referencing the user table's primary key as the foreign key data table will be automatically deleted You only need to manipulate the data table once.

There is a phpmyadmin in the temporary not directly set the foreign key function, can be modified by the command line, the format is as follows
ALTER TABLE table name
Add foreign key field references table name (field)

You can easily set foreign keys using the Navicat tool:
The right mouse button opens the Design table interface.

The above tab selects the foreign key to add

But I was having problems adding foreign keys.

I first created two tables Tb_user and Tb_device, the storage engine is InnoDB, and all have user_id this property (the type is exactly the same), when the foreign key was added error occurred

Error:cannot Add or update a child row:a FOREIGN KEY constraint fails

Finally, it turns out that the data already exists in my tb_device table, so if you add a foreign key, it will cause an error.
the solution :
Delete data, and then add foreign keys

From for notes (Wiz)

MySQL Add foreign KEY constraint