MySQL learning 9: foreign key constraints in MySQL

This article describes the foreign key constraints in MySQL. 1 constraints Overview The purpose of creating constraints is to ensure data integrity and consistency. The constraint is divided into table-level constraints and column-level constraints based on the number of fields targeted by the constraint. If the constraints are divided by function, they are: NOTNULL (non-empty constraint), PRIMARYKEY (primary key constraint), and UNIQUEKEY

This article describes the foreign key constraints in MySQL. 1 constraints Overview The purpose of creating constraints is to ensure data integrity and consistency. The constraint is divided into table-level constraints and column-level constraints based on the number of fields targeted by the constraint. If the constraints are divided by function, they are: not null (non-empty constraint), PRIMARYKEY (primary KEY constraint), and unique key.

This article describes the foreign key constraints in MySQL.

1 constraints Overview

The purpose of creating constraints is to ensure data integrity and consistency. The number of fields to be restricted is divided into table-level constraints and column-level constraints.

Constraints.

If the constraints are divided by function, they are: not null (non-empty constraint), primary key (primary key constraint), and UNIQUE.

KEY (unique constraint), DEFAULT (DEFAULT constraint), and foreign key (foreign key constraint ).

Previously, we have initially covered four constraints except key constraints. This time we will talk about the most complex foreign key constraints.

Requirements for two foreign key constraints

Foreign key (foreign key constraint)

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

The constraints are complex. Therefore, foreign key constraints must meet the following requirements:

1) the parent and child tables must use the same storage engine, and temporary tables are not allowed. When talking about the parent and child tables, let's look at their definitions: The child table refers

A table with a foreign key column is called a sub-table; a parent table is called a parent table referenced by a sub-table.

2) The data table storage engine can only be InnoDB.

3) Foreign key columns and reference columns must have similar data types. The length of the number or whether there are symbols must be the same, while the length of the character can not

. When it comes to foreign key columns and reference columns, let's take a look at the definition: foreign key columns refer to columns with the foreign key keyword added to the column, and reference columns refer

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

4) indexes must be created for foreign key columns and reference columns. If the foreign key does not have an index, MySQL automatically creates an index. If no index exists in the reference column

MySQL does not automatically create indexes.

Three storage engines

To create foreign key constraints, you need to understand the storage engine. Here is a brief description.

(1) What is a storage engine?

The storage engine is also called the table type. It refers to the data table storage mechanism, index scheme, and other related functions. Different engines have different processing methods

To optimize different features or functions, and select a suitable storage engine based on actual needs. Storage engine types include MyISAM and InnoDB.

(2) differences between MyISAM and InnoDB (1) InnoDB supports foreign keys, but MyISAM does not.

2) MyISAM each table generates three files: table. MYI (index file), table. MYD (data file), table. frm (table structure file)

Each table in Innodb has only one table. frm file. The data of all Innodb Engine tables will be stored in ibdata.

3) MyISAM supports table-level locks. Its advantage lies in insertion and Retrieval. Innodb supports row-level locks and its advantage lies in update and deletion.

4) Innodb supports transactions.

(3) edit the default storage engine of the data table

1) First, find the MySQL configuration file MY. ini in the MySQL installation directory and double-click it;

2) Check whether: default-storage-engine = INNODB

3) after modification, You need to restart the MySQL service. Otherwise, you do not need to restart.

Create foreign key constraint (1) create a province data table as the parent table, and then check the data table creation Information to check whether the engine is InnoDB:

Create table provinces (

Id smallint unsigned primary key AUTO_INCREMENT,

Pname VARCHAR (20) NOT NULL

);

Show create table provinces;

This ensures that the default engine InnoDB is used when creating a data table. The following users table is also InnoDB.

(2) Use foreign key constraints to create another similar data table as a sub-table.

Create table users (

Id smallint unsigned primary key AUTO_INCREMENT,

Username VARCHAR (10) not null,

Pid BIGINT,

Foreign key (pid) REFERENCES provinces (id)

);

An error message is displayed when you create a data table: ensure that the data types are consistent.

If the data type is the same, the default value is symbol bit:

Create table users (

Id smallint unsigned primary key AUTO_INCREMENT,

Username VARCHAR (10) not null,

Pid SMALLINT,

Foreign key (pid) REFERENCES provinces (id)

);

There will still be an error message: whether to maintain the same signed or not.

Change to a consistent data class and whether the data class has a signed bit:

Create table users (

Id smallint unsigned primary key AUTO_INCREMENT,

Username VARCHAR (10) not null,

Pid smallint unsigned,

Foreign key (pid) REFERENCES provinces (id)

);

When creating a data table, we know that the sub-table is users, the parent table is provinces, the pid in the sub-Table users is a foreign key column, and the pid in the parent table provinces

The id field is a reference column. This ensures that the foreign key column and reference column must have similar data types.

(3) check whether the foreign key column and reference column create an index. (1) check whether the reference column id in the parent table provinces automatically creates an index:

Show indexes from provinces;

Show indexes from provinces \ G;

The above indicates that the index has been created for the reference column. We are more accustomed to seeing that the index column is the second command, so we generally use the latter.

2) check whether the foreign key column creates an index.

Show indexes from users \ G;

The above results indicate that the users table has two index columns, one is the id field of the users table, the other is the pid field of the foreign key column, and its reference column is the parent table.

The id field of the reference column in provinces.

We can also check whether 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. After the above steps, we have successfully used the foreign key constraint. There are many relationships

The foreign key constraint operations are not described too much.