MySQL Add foreign KEY constraint

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.