mysql advanced (11) The role of foreign keys in the database

黄舟

Release： 2017-02-10 10:45:50

Original

1468 people have browsed it

MySQLThe role of foreign keys in the database

## MySQLThe purpose of foreign keys is to control the storage in foreign key tables The data is related to the two tables. It is a very important part of the MySQL database and is worthy of our in-depth understanding. So, MySQLWhat role do foreign keys play? The following will take you to explore the secrets.

MySQL

The role of foreign keys

The main purpose of maintaining data consistency and integrity is to control the data stored in foreign key tables. To associate two tables, foreign keys can only reference the values of columns in the table!

Example

1

a b

Two tables

The table contains customer number and customer name

The order of each customer is stored in the table

With the foreign key, you can only confirm

b Customer x can be deleted from the a table only after there is no order from customer x.

#Prerequisites for establishing a foreign key

The columns of this table must be of the same type as the foreign key

(The foreign key must be the primary key of the foreign key).

Specify the primary key keyword:

foreign key(Column name)

Quote the foreign key keyword:

references <Foreign key table name>(Foreign key column name)

Event trigger limit

on delete

and on update, can set the parametercascade(follows the external Key changes), restrict(Restrict foreign key changes in the table), set Null(SET NULL value),set Default（set default value）,[Default]no action

Example

2

outTable

table primary key id type int

Create Table containing foreign keys:

 create table temp(
        id int,
        name char(20),
        foreign key(id) references outTable(id) on delete cascade on update cascade);

Copy after login

Description: Set the

id column to the MySQL foreign key, refer to the table The id column of outTable. When the value of the foreign key is deleted, the corresponding column in this table is deleted;When the value of the foreign key changes, the value of the corresponding column in this table changes.

Note

MySQL

A table can only have one primary key, and the primary key can be composed of multiple fields.

美文美图

The above is the role of mysql advanced (11) foreign keys in the database Content, please pay attention to the PHP Chinese website (m.sbmmt.com) for more related content!