mysql--foreign KEY (froeign key)-----------MySQL foreign key use detailed

If a field of one entity points to the primary key of another entity, it is called a foreign key
The entity that is pointed to is called the primary entity (the primary table), also called the parent entity (the parent table).
The entity that is responsible for pointing, called from the entity (from the table), also called the Child Entity (sub-table)

Role:
Used to constrain entities that are within a relationship
When a child table record is added, does it have a parent table record corresponding to it

If the primary table does not have related records, the table cannot be inserted from

Insert the main table data before inserting the data from the table:

How a table should handle related records when deleting or updating a master table record

To set cascading actions:
How the table data associated with the primary table data should be handled when it changes
Use keywords:
On update
On delete
To identify
allowable Cascade actions:
Cascade associated operation, if the primary table is updated or deleted, the corresponding action is also taken from the table
Set NULL, indicating that no records from table data are pointing to the primary table
Restrict: Rejecting the related operation of the primary table

ALTER TABLE T_student add foreign key (class_id) references T_class (class_id)
On delete set null; # When a foreign key is deleted, the foreign key value from the table is set to NULL

To modify a foreign key:
Remove the foreign key from the table before adding
ALTER TABLE Tb_name drop froeign key FOREIGN key name
Foreign key names can be customized when creating foreign keys, and if not customized, a name is automatically generated according to MySQL
Show CREATE TABLE tb_name;

ALTER TABLE t_student drop foreign key t_student_ibfk_1;

Deleting a foreign key does not have any effect on the data in the table, only one constraint on the table is changed

ALTER TABLE T_student add foreign key (class_id) references T_class (class_id)
On delete set null; # When a foreign key is deleted, the foreign key value from the table is set to NULL

Note:on delete and on update can appear at the same time,
However, Cascade, set NULL, restrict cannot occur at the same time after the on delete or on update, there can be only one
FOREIGN key constraints on restrict are not written here, as with the other two
Restrict: Reject the related operation of the primary table, when the primary table updates or deletes data, there is data related to the primary table primary key in the table, then the main table data is not allowed to be updated or deleted

When you do not set any cascading relationship constraints, the primary table defaults to restrict

Under Restrict constraints, if you want to delete the primary table data, in addition to deleting data that is not related to the child table data,
You can modify the foreign key in the child table first (when modified, the foreign key must also be associated to the primary key of the main table, otherwise it cannot be modified successfully)

You can also delete the primary table data that you want to delete before deleting the main table data.

Original website: http://www.cnblogs.com/zunpeng/p/3878459.html

Recently started to do a lab management system because of the number of tables to be stored • So maintain the association between tables • • Studied the foreign keys of MySQL.

(1) Only innodb types of tables can use foreign keys, mysql default is MyISAM, this type does not support foreign key constraints

(2) The advantage of foreign key: can make two tables association, ensure the consistency of data and realize some cascade operation;

(3) The role of the foreign key:

Maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table. Make two tables associative, and foreign keys can only refer to the values of columns in the outside!

(4) The premise of establishing a foreign key:

Two tables must be of the InnoDB table type.
The field that uses the foreign key relationship must be an indexed type (index).
Fields that use foreign key relationships must be similar to data types

(5) Steps to create

Specify PRIMARY KEY keyword: foreign key (column name)

Referencing foreign key keywords: references < foreign key table name > (foreign key column name)

(6) Event trigger limit: On delete and on UPDATE, set parameter cascade (following foreign key changes), restrict (Restrict foreign key changes in appearance), SET null (null value), set default, [Default]no Action

(7) Example

Outtable table primary key ID type int

To create a table that contains a foreign key:

Copy CodeThe code is as follows:
CREATE TABLE Temp (
ID int,
Name Char (20),
FOREIGN key (ID) references outtable (ID) on the DELETE cascade on UPDATE cascade);

Description: Set the ID column to foreign key reference the ID column of the outer outtable when the value of the foreign key is removed from the corresponding column in this table except when the value of the foreign key changes the corresponding column value in this table.

Copy CodeThe code is as follows:
CREATE TABLE temp (id int, name char, foreign key (ID) references outtable (ID) on the DELETE cascade on UPDATE cascade);

Cons: Optimizations like query caching, index caching, and so on for MySQL do not work for INNODB types of tables, and synchronous replication in the overall database schema is not effective for INNODB types of tables. Like a database in the core of the table like a commodity table please try not to use foreign keys, if the synchronization is sure to synchronize the commodity library, plus the foreign key also can not pass, optimization also does not work, it is not worth the candle, do the purpose of foreign keys to ensure data integrity, please pass the program to achieve this purpose and not foreign keys, remember

Original website: http://www.jb51.net/article/62971.htm

mysql--foreign KEY (froeign key)-----------MySQL foreign key use detailed