Talking about MySQL foreign key

http://www.xiaoxiaozi.com/2009/07/12/1158/, a relational database management system like MySQL, is based on the ability to create relationships between tables in a database. By easily establishing a record-to-record connection in different tables, the RDBMS can analyze the data in different ways, while keeping the database organized in a systematic way and with minimal redundancy.

Simple description:
These relationships are basically managed by a foreign key , which joins the records in different tables as public parts in all tables in the relationship that have the same meaning. A foreign key can be one-to-two, a table record can only be connected to one record of another table, or one-to-many, and a table record is connected to multiple records from another table.

The definition of "key" and "index" in MySQL is the same, so the foreign key and the primary key are also one of the indexes. The difference is that MySQL automatically indexes the primary keys for all tables, but the foreign key fields must be explicitly indexed by the user. this is the same as some of the more heavily feudal families, foreign children (daughter-in-law, inverted into) are generally not respected.

Vulgar Example:

Examples of one-to-one relationships between tables:

There are two tables, the first of which is the basic table that records how many people are in the company, who they are, the employee number and the employee's name. Another table records how much wages are paid to users each month, so-called payroll is also.

But the payroll can not be the name of the employee as the primary key, also through the employee ID, because the employee's name is likely to repeat AH. Department manager called Zhang San, younger brother also called Zhang San, then these two Zhang San wages can be same? And everyone in the employee table has a salary, otherwise no one will give you work, and a person can only have a salary, otherwise the boss also disagree. so the employee and Payroll tables are related to one-to-a relationships through employee IDs .

But we should have a good value, we can not work for the money, we are to learn knowledge, learn culture, for the early realization of four modernizations (don't ask me what, also don't ask me in the end do not) and strive. So don't shout in the salary table if you don't have it. Well.

/*
Create an Employee table
*/
CREATE TABLE Employees (
ID int (5) NOT NULL auto_increment,
Name varchar (8) is not NULL,
Primary KEY (ID)
)
Type=innodb;

/*
Set up a payroll table
*/
CREATE TABLE Payroll (
ID Int (5) is not NULL,
emp_id Int (5) is not NULL,
Name varchar (8) is not NULL,
Payroll float (4,2) not NULL,
Primary key (ID),
Index emp_id (emp_id),
Foreign KEY (emp_id) references employees (ID)
)
Type = InnoDB;

Example of a one-to-many relationship between tables:

There are two tables, one is the corrupt officials table, there are corrupt officials ID and name. There is a corrupt official mistress table, note that a corrupt official is not necessarily only a mistress, it has a 2345 milk is very normal, so in the corrupt officials table inside a data, corresponding to the Mistress list may have more than one record, which is through the corrupt official ID to correlate a one-to-many relationship .

Referential integrity:

When a foreign key is related to a field in another table, and the relationship is temporary, the system is called a state of referential integrity. That is, if a field appears only once in all tables, and the change in this field of each table affects the other tables, there is referential integrity.

The terminology may not be easy to understand, in fact, to maintain consistency of all data in tables that have foreign keys. For example, "Zhang San" quit, in the employee table there must be no such person, but if there is still a child in the salary table, then the eldest brother will be very angry .

In addition, for example, a magistrate, because some small achievements, from the magistrate to become a magistrate, then his mistress of the status also to adjust, at least from the magistrate mistress changed to the Magistrate mistress, otherwise the mistress will not agree .

The MySQL foreign key can only be used in the InnoDB table:

Today's mainstream databases automatically consider referential integrity issues. When you update or delete data, it will change the data in the associated tables to you. Biru official Zhang San renamed King Two Leper, the title of his mistress will automatically changed to Wang Er leper's mistress. Well.

MySQL has always been a wait-and-see attitude, which allows the use of foreign keys, but for the purpose of integrity checking, this functionality is ignored in all table types except for the InnoDB table type. This may be weird, but it's actually quite normal: for each INSERT, update, and delete of all foreign keys for a database, an integrity check is a time-consuming and resource-intensive process that can affect performance, especially when dealing with complex or entangled connection trees. As a result, users can choose the best combination for specific needs on a table basis. .

Therefore, if you need better performance and do not need integrity checking, you can choose to use the MyISAM table type, and if you want to build the table in MySQL based on referential integrity and want to maintain good performance on this basis, it is best to choose the table structure as the InnoDB type.

MySQL creates foreign key syntax:

The syntax for creating foreign keys is this: FOREIGN key (the field name of the current table) ... REFERENCES reference table (Reference table field name)

Foreign KEY (emp_id) references employees (ID); This means that the emp_id field of the current table is a foreign key with the ID of employees.

Precautions:

• Once a foreign key is created, MySQL only allows data columns that are already in the foreign key table to be added to the current table. For example, the corrupt officials have "king two leper", then the Mistress table only can have "Wang er leper mistress." That is, only to confirm that a person is a corrupt official, in order to put his mistress information in this table, otherwise it is no drop.
• All tables in the relationship must be InnoDB tables, and in non-InnoDB tables, MySQL will ignore foreign KEY ... The references modifier.
• The fields used for foreign key relationships must be explicitly indexed in all reference tables, and InnoDB cannot automatically create indexes.
• In a foreign key relationship, the data type of the field must be similar, which is especially important for integer types that must match both size and symbol.
• MySQL allows us to delete a table even if the table has a foreign key constraint, and does not produce an error (even if doing so may break the foreign key created earlier)

To delete a foreign key method:

Long ago, people could only delete a foreign key by deleting the table. But now MySQL (in 4.0.13 and later) provides a way to reduce the ease of removing foreign keys from a table, but it's not clear, but at least it's no longer so shameless.

ALTER TABLE table-name DROP FOREIGN KEY Key-id;

Here is a concept, what is the ID of this foreign key? We can get the value of Key-id by the Show CREATE Table command. We will discuss this in detail in the future and you can demonstrate it yourself.

/*
Display the Build table structure statement, Key-id to Payroll_ibfk_1
*/
Show CREATE TABLE Payroll \g
/*
1. Row ***************************
Table:payroll
Create table:create Table ' Payroll ' (
' ID ' int (5) is not NULL,
' emp_id ' int (5) is not NULL,
' Name ' varchar (8) Not NULL,
' Payroll ' float (4,2) not NULL,
PRIMARY KEY (' id '),
KEY ' emp_id ' (' emp_id '),
CONSTRAINT ' Payroll_ibfk_1 ' FOREIGN KEY (' emp_id ') REFERENCES ' employees ' (' ID ')
) Engine=innodb DEFAULT charset=latin1
1 row in Set (0.00 sec)

*/

Auto key update and delete:

The foreign key guarantees the integrity of the newly inserted record. But what happens if I delete a record from a named table in a references clause? What happens in a secondary table that uses the same value as a foreign key?

Obviously, those records should also be deleted, otherwise there would be a lot of meaningless orphaned records in the database. MySQL may pass to foreign KEY ... The REFERENCES modifier adds an on delete or on UPDATE clause to simplify the task, which tells the database how to handle orphaned tasks in this situation.

Key words	Meaning
CASCADE	Delete all records that contain a referential relationship to the deleted key value
SET NULL	Modify all records that contain a reference relationship to the deleted key value, replace with a null value (only for fields that have been marked as NOT NULL)
RESTRICT	Deny delete requirements until a secondary table using the DELETE key value is manually deleted and no references are available (this is the default and the safest setting)
NO ACTION	You don't do anything.

Note that with the on UPDATE and on delete rules, setting MySQL to perform automatic operations can result in serious data corruption if the key relationship is not set. For example, if a series of tables are connected by a foreign key relationship and an on delete CASCADE rule, any change in the primary table will cause even only the original deletion of some records that will be contacted to be deleted without warning. So, we still have to check these rules before we operate, and we need to check them again after the operation.

Talking about MySQL foreign key