How to query the foreign key constraints of a table in mysql?

Mysql method to query the foreign key constraints of a table: use the "SHOW CREATE TABLE" statement, the syntax format "SHOW CREATE TABLE ;", you can display the use of the data table name All constraints, including primary key constraints, foreign key constraints, non-null constraints, unique constraints, etc.

(Recommended tutorial: mysql video tutorial)

You can use the SHOW CREATE TABLE statement in MySQL 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 <数据表名>;

Example

Create the data table tb_emp8 and specify id as the primary key constraint and name as the unique Constraints, deptId is a non-null constraint and a foreign key constraint, and then check the constraints in the table. The results of the SQL statement 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 \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)

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