How to query foreign key constraints in mysql

In mysql, you can use the show statement to query foreign key constraints. The show statement can display the database, table and column information in mysql. The syntax is "SHOW CREATE TABLE table name"; this statement can display the information in the table. Information about all primary key constraints, foreign key constraints, non-null constraints and other constraints.

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

How to query foreign key constraints in 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.

View the constraint syntax format in the data table as follows:

SHOW CREATE TABLE <数据表名>;

The example is 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 \G
*************************** 1. row ***************************
       Table: tb_emp8
Create 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_emp8_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.19 sec)

Extended knowledge:

Definition of foreign key constraints

Mysql foreign key constraint (FOREIGN KEY) is a special field of the table, often used together with primary key constraints. For two tables with an associated relationship, the table where the primary key in the associated field is located is the main table (parent table), and the table where the foreign key is located is the secondary table (child table)

Suppose we have 2 Tables, namely table A and table B, are related through a common field "id". We call this relationship R. If "id" is the primary key in table A, then table A is the primary table in this relationship R. Correspondingly, table B is the slave table in this relationship, and the "id" in table B is what table B uses to reference the data in table A, which is called a foreign key. Therefore, the foreign key is the public field used to reference the data in the main table from the table

The role of foreign key constraints

Foreign key constraints can help us determine The reference relationship between the foreign key field in the slave table and the primary key field in the master table can also ensure that the master 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 from the main table, the corresponding record from the slave table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be a null value. If it is not a null value, the value of each foreign key must be equal to a certain value of the primary key in the main table.

However, foreign keys Key constraints are costly and require consuming system resources, and may not be suitable for large concurrent SQL operations. Therefore, 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. This is also the reason why we can perform related queries even if we do not set foreign keys

Recommended learning:mysql video tutorial

The above is the detailed content of How to query foreign key constraints in mysql. For more information, please follow other related articles on the PHP Chinese website!