Usage of MySQL Foreign keys

 

A relational database management system like MySQL is based on the ability to create relationships between database tables. By easily establishing a record-to-record relationship in different tables, RDBMS can analyze data using different methods, while keeping the database organized in a systematic manner with minimal redundancy.

Simple Description:
These relationships are basically managed by foreign keys.In the relationship, fields with the same meaning are used as common parts to connect records in different tables. The foreign key can be one-to-one. The record of one table can only be connected to one record of another table, or one-to-many. The record of one table is connected to multiple records of another table.

MySQL has the same definition of "key" and "Index", so foreign keys and primary keys are also an index. The difference is thatMySQL automatically indexes the primary keys of all tables, but the foreign key field must be explicitly indexed by the user.This is the same as some families with heavy feudal ideology. Foreign Children (daughter-in-law and daughter-in-law) are generally not valued.

Vulgar example:

Example of one-to-one relationship between tables:

There are two tables. The first table records the number of employees in the company, who are there, that is, the employee ID and employee name. The other table records the salaries of users each month.

However, the payroll table cannot use the employee name as the primary key. The employee ID must also be used because the employee name may be repeated. The Department Manager is Zhang San, and the younger brother is also Zhang San. Can their salaries be the same? In addition, everyone in the employee's table has a salary. Otherwise, no one will work for you, and only one person can have a salary. Otherwise, the boss does not agree.Therefore, the employee table and the payroll table are associated by employee ID in a one-to-one relationship..

However, we need to have a good value. We cannot go to work for money. We want to learn knowledge, learn culture, and achieve four modernization as soon as possible (don't ask me what it is, don't ask me if I actually do not. So don't yell if you don't have it in the payroll. Well.

/*
Create employee table
*/
Create Table employees (
Id int (5) not null auto_increment,
Name varchar (8) Not null,
Primary Key (ID)
)
Type = InnoDB;

/*
Create payroll
*/
Create Table payroll (
Id int (5) not null,
Emp_id int (5) not null,
Name varchar (8) 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-multiple relationship between tables:

There are two tables: a corrupt official table with the ID and name of the corrupt official. There is another form of a corrupt official MIIT. Note that a corrupt official does not necessarily have only one MIIT. It is normal to have a 2345-Year-Old MIIT. Therefore, a piece of data on the corrupt official form is provided, there may be multiple records in the MIIT table,This is a one-to-multiple relationship associated with the corrupt official ID..

Integrity of reference:

When the foreign key has a relationship with the field of another table and this relationship is unique, this system is called in the state of reference integrity. That is to say, if a field appears only once in all tables, and the changes to this field in each table will affect other tables, there is reference integrity.

The term may not be easy to understand. In fact, it is necessary to maintain the consistency of all data in tables with foreign keys. For example, if Michael leaves, the employee's table will certainly not contain this person,However, if the child still exists in the payroll, the boss will be very angry..

In addition, for example, if a county magistrate changes from a county magistrate to a Zhifu due to some minor achievements, the status of his mistresses should also be adjusted,At least change from the second milk of the county magistrate to the second milk of Zhifu. Otherwise, the second milk will not agree..

MySQL Foreign keys can only be used in InnoDB tables:

In today's mainstream databases, the integrity of the reference is automatically considered. When you update or delete data, it also changes the data in the associated table. For example, if Zhang San, the county magistrate, is renamed as Wang Erma Zi, his mistress title will be automatically changed to Wang Erma's mistress. Well.

MySQL has been holding a wait-and-see attitude towards this. It allows the use of foreign keys, but this function is ignored in all table types except InnoDB table types for the purpose of integrity testing. This may be a bit weird, but it is actually quite normal:Integrity check is a time-consuming and resource-consuming process for each insertion, update, and deletion of all foreign keys of the database. It may affect performance, especially when dealing with complex or winding connection trees. Therefore, you can select the best combination suitable for specific needs based on the table..

Therefore, if you need better performance and do not need integrity check, you can select the MyISAM Table type, if you want to create a table in MySQL Based on the integrity of the reference and maintain good performance on this basis, it is best to select the table structure as InnoDB type.

MySQL foreign key creation Syntax:

The syntax for creating a foreign key is as follows: foreign key (the Field name of the current table )... References reference table (reference table field name)

Foreign key (emp_id) References employees (ID); that is, the emp_id field of the current table uses the ID field of employees as the foreign key.

Note:

• Once a foreign key is created, MySQL only allows adding existing data columns to the current table. For example, if an official form contains "Wang Erma zi", only the "Wang Erma Zi's mistress" can be available in the mistresses ". That is to say, only when a person is determined to be corrupt can he include his or her mistress information in this table.
• All tables in the link must be InnoDB tables. In non-InnoDB tables, MySQL will ignore foreign key... References modifier.
• Fields used for foreign key relationships must be explicitly indexed in all reference tables. InnoDB cannot automatically create indexes.
• In the foreign key relationship, the Data Type of the field must be similar, which is especially important for integer types that both the size and symbol must match.
• Even if the table has a foreign key constraint, MySQL allows us to delete the table without generating an error (even if this operation may damage the foreign key created earlier)

To delete a foreign key:

Long long ago, foreign keys can only be deleted by deleting tables. However, MySQL (in versions 4.0.13 and later) now provides a method for easing the deletion of Foreign keys from the table, but it is not clear whether to ease it, but at least it is not so shameless.

Alter table table-Name drop foreign key-ID;

Here is a concept. What is the ID of this foreign key? We can use the show create table command to obtain the key-id value. We will discuss this in detail in the future. You can demonstrate it on your own.

/*
Displays the table creation statement. The key-ID is payroll_ibfk_1.
*/
Show create table payroll/g
/*
* *************************** 1. row ***************************
Table: payroll
Create Table: Create Table 'payroll '(
'Id' int (5) not null,
'Emp_id' int (5) not null,
'Name' varchar (8) Not null,
'Payroll' float () not null,
Primary Key ('id '),
Key 'emp' _ id' ('emp' _ id '),
Constraint 'payroll _ ibfk_1 'foreign key ('emp_id') References 'ployees' ('id ')
) Engine = InnoDB default charset = Latin1
1 row in SET (0.00 Sec)

*/

Automatic Key Update and deletion:

The foreign key ensures the integrity of the newly inserted records. However, what if I delete a record from a named table in the 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 will be many meaningless isolated records in the database. MySQL may forward... The references modifier adds an on delete or on update clause to simplify tasks. It tells the database how to handle isolated tasks in this case.

Keywords	Description
Cascade	Delete all records with reference to the deleted key value
Set null	Modify all records that have reference relationships with deleted key values and replace them with null values (only for Fields marked as not null)
Restrict	Reject the deletion request until the secondary table that uses the deleted key value is manually deleted and has no reference (this is the default setting and the safest setting)
No action	Nothing

Note that when you set MySQL to perform automatic operations through the on update and on Delete rules, if the key relationship is not set properly, it may cause serious data damage.For example, if a series of tables are connected through a foreign key relationship and the on Delete cascade rule, any changes to the primary table will cause some records that will be associated with the original deletion to be deleted without warning. Therefore, we still need to check these rules before the operation, and check again after the operation.