MySQL Learning 9: A detailed explanation of foreign key constraints in MySQL

This article mainly explains the foreign key constraints in MySQL.

A constraint overview

The purpose of creating a constraint is to ensure the integrity and consistency of the data. Constraints are divided into table-level constraints and column-level based on how many fields are constrained by the constraint

Constraints.

If the constraint is divided by function, the engraving is divided into: NOT null (non-null constraint), PRIMARY key (primary KEY constraint), UNIQUE

The key (Unique constraint), default (constraint), and foreign KEY (FOREIGN KEY constraint).

We have initially covered four constraints outside the exception key constraint, and this time we'll talk about the most complex foreign key constraints.

Two requirements for foreign KEY constraints

FOREIGN key (FOREIGN KEY constraint)

The purpose of creating a foreign key constraint is to maintain data consistency, integrity, and to implement one-to-one or one-to-many relationships. Since the foreign KEY constraint is compared to the other four

Constraints are complex, so the requirements for foreign key constraints are as follows:

1) The parent and child tables must use the same storage engine and prohibit the use of temporary tables. Speaking of the parent and child tables, look at their definition: the child table refers to the

A table with a foreign key column is called a child table, and a parent table refers to a table referenced by a child table called a parent table.

2) The storage engine for the data table can only be innodb.

3) The Foreign key column and the reference column must have similar data types. Where the length of the number or whether the sign bit must be the same, and the length of the character can not

With. Speaking of foreign key columns and reference columns, let's look at the definition: The Foreign key column refers to the column with the FOREIGN key keyword column called the foreign key column; Reference column refers to the

The column referenced by the foreign key column is referred to as the Reference column.

4) The Foreign key column and the reference column must create an index. If no index exists for the foreign key, MySQL will automatically create the index. If the reference column does not exist in the index

, MySQL does not automatically create an index.

three storage engines

Since creating a FOREIGN key constraint requires an understanding of the storage engine, here's a brief explanation.

(1) What is a storage engine

The storage engine is also called the table type, refers to the data table storage mechanism, indexing scheme and other ancillary functions. Different engines due to different processing methods, will bring

to the different functions or functions of the optimization, according to the actual need to choose the appropriate storage engine. The storage engine types are divided into MyISAM and InnoDB.

(2) difference between MyISAM and InnoDB 1) InnoDB support foreign key, MyISAM not supported.

2) MyISAM each table will generate 3 files: table. MYI (index file), table. MYD (data file), TABLE.FRM (table structure file)

InnoDB each table has only one table.frm file, the data for all INNODB engine tables will be stored inside the ibdata*.

3) MyISAM Support table-level lock, the advantage lies in the insertion and retrieval, INNODB support row-level lock, the advantage is to update and delete.

4) InnoDB support transactions.

(3) editing the default storage engine for data tables

1) First find the MySQL configuration file in the MySQL installation directory My.ini, double-click to open;

2) Check if there is: Default-storage-engine=innodb

3) The MySQL service needs to be restarted after modification, otherwise it will not be restarted.

Four creating a FOREIGN key constraint (1) Create a province data table as the parent table, and then view the data table creation information to confirm if the engine is InnoDB:

CREATE TABLE Provinces (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

PName VARCHAR () not NULL

);

SHOW CREATE TABLE Provinces;

This ensures that the data table is created with the default engine InnoDB. The following users table is also InnoDB.

(2) Use a FOREIGN key constraint to create another similar data table as a child table.

CREATE TABLE Users (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (Ten) is not NULL,

pid BIGINT,

FOREIGN KEY (PID) REFERENCES provinces (ID)

);

The above created data table will have an error message: You need to ensure that the data type is consistent.

Modified to be of the same data type, the default is signed bit:

CREATE TABLE Users (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (Ten) is not NULL,

pid SMALLINT,

FOREIGN KEY (PID) REFERENCES provinces (ID)

);

There will still be error messages: This time is whether to keep the same unsigned bit.

Modify to a consistent data class and whether there is a sign bit:

CREATE TABLE Users (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (Ten) is not NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY (PID) REFERENCES provinces (ID)

);

The process of creating a data table we know that the child table is the users, the parent table is provinces; the PID in the child table users is the foreign key column, and the parent table provinces

The ID field is the Reference column. Thus we guarantee that the foreign key column and the reference column must have similar data types.

(3) to see if the foreign key column and the reference column create an index 1) See if the Reference column ID in the parent table provinces automatically creates an index:

SHOW INDEXES from provinces;

SHOW INDEXES from Provinces\g;

All of these indicate that the reference column has been indexed, and we are more accustomed to seeing the second type of command for the indexed column, so we will generally use the latter.

2) again to see if the Foreign key column creates an index

SHOW INDEXES from Users\g;

The above results indicate that the data table users have two index columns, one is the ID field of the Users table, one is the Foreign key column PID field, and its reference column is the parent table

in Provinces The Reference Column ID field.

We can also see if the PID in the user table is a foreign key column

SHOW CREATE TABLE users;

This proves that the PID field in the Users data table is indeed a foreign key constraint, and we successfully used the FOREIGN KEY constraint after the above steps. There are many relationships with

The operation of FOREIGN key constraints, we will not be too much introduction.

MySQL Learning 9: A detailed explanation of foreign key constraints in MySQL