MySQL FOREIGN key

1, what is the role of foreign keys and foreign keys

If a field in a table that has a non-primary key points to a primary key in a table, the field is called a foreign key.

A foreign key is used in a Inodb table, and MyISAM is not available even if the statement created does not have a problem. In fact, it is not possible to create a successful, it is recommended to create a SQL statement, with the engine inodb.

The role of foreign keys: to maintain the consistency and integrity of data.

2, foreign key creation

1. Create a province table, which can be called a parent table.

Create Table Province (     pId int primary key auto_increment,     pName varchar) engine InnoDB;

　　

2. Create a user table and create a foreign key. Where the PID is the foreign key. He is actually the primary key of the parent table.

CREATE TABLE User (userId int primary key auto_increment,username varchar (+), PID int,foreign key (PID) References province (pId)) Engine InnoDB;

　　

foreignkey(本表字段) referencesprovince(父表字段)

 

Note:

The above refers to the foreign key to maintain data consistency and integrity.

To insert data into the user table, you need to determine the ID of the province table. Otherwise the data cannot be inserted

Delete the data inside the province, you need to delete the data inside the user table, otherwise you cannot delete the data inside the province. Because the user table references the data inside the province.

Deleting a table is, of course, the same. This ensures consistency and integrity of the data.

Operation specific into the following:

1, insert the data into the user table, you can see the error. This is because there is no record of PID 1 in the parent table.

2, for this need to first add a PID 1 record in the parent table. You can find a record with ID 1 inside the parent table, and then insert it into the user table.

3, delete the records of the province table, you can see that cannot be deleted. You can delete records for the user table before deleting province records.

3, foreign key misunderstanding (easy to confuse the place, in the end who is the foreign key. ）

Here is the user table is a table referencing province, then province is the parent table, the user table is a child table. The foreign key PID of the user is actually the primary key of the parent table of the province.

MySQL FOREIGN key