Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to query foreign key constraints related knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone after reading this mysql how to query foreign key constraints article will have a harvest, let's take a look.

In mysql, you can use show statements to query foreign key constraints, and show statements can display information about databases, tables and columns in mysql with the syntax "SHOW CREATE TABLE table name"; this statement can display information about all primary key constraints, foreign key constraints, non-null constraints and other constraints in the table.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query foreign key constraints by mysql

The mysqlshow command is used to display database, table, and column information in the MySQL server.

In MySQL, you can use the SHOW CREATE TABLE statement to view the constraints in the table and then query the foreign key constraints.

The format of the constraint syntax in the view data table is as follows:

SHOW CREATE TABLE

Examples are as follows:

Mysql > CREATE TABLE tb_emp8-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (22) UNIQUE,-> deptId INT (11) NOT NULL,-> salary FLOAT DEFAULT 0,-> CHECK (salary > 0),-> FOREIGN KEY (deptId) REFERENCES tb_dept1 (id)->) Query OK, 0 rows affected (0.37 sec) mysql > SHOW CREATE TABLE tb_emp8\ Graph * 1. Row * * Table: tb_emp8Create Table: CREATE TABLE `tb_ emp8` (`id`int (11) NOT NULL, `name` varchar (22) DEFAULT NULL, `deptId` int (11) NOT NULL, `salary`float DEFAULT'0' PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `deptId` (`deptId`), CONSTRAINT `tb_ 1` FOREIGN KEY (`deptId`) REFERENCES `tb_ dept1` (`id`) ENGINE=InnoDB DEFAULT CHARSET=gb23121 row in set (0.19 sec)

Expand knowledge:

Definition of foreign key constraint

A Mysql foreign key constraint (FOREIGN KEY) is a special field of a table and is often used with primary key constraints. For two associated tables, the table with the primary key in the associated field is the master table (parent table), and the table with the foreign key is the slave table (child table).

Suppose we have two tables, Table An and Table B, which have an association through a common field "id". We call this association R. If "id" is the primary key in table A, then table An is the primary table in the relationship R. Accordingly, Table B is the slave table in this relationship, and the "id" in Table B is used to refer to the data in Table A, which is called foreign key. So, the foreign key is the public field that is used from the table to reference the data in the main table.

The role of foreign key constraints

Foreign key constraints can help us determine the reference relationship between the foreign key fields in the slave table and the primary key fields in the primary table, and ensure that the primary table data referenced by the data in the slave table will not be deleted, ensuring the consistency of the data in the two tables.

When a record is deleted by the master table, the corresponding record in the slave table must be changed accordingly. A table can have one or more foreign keys, which can be null. If not, the value of each foreign key must be equal to a value of the primary key in the primary table.

However, foreign key constraints are costly and consume system resources, and may not be suitable for large concurrent SQL operations. So mysql allows you to complete the logic of checking data consistency at the application level without using the foreign key constraints that come with the system. That's why we can make associative queries even if we don't have a foreign key set.

This is the end of the article on "how to query foreign key constraints in mysql". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to query foreign key constraints in mysql". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.