REFERENTIAL_CONSTRAINTS

REFERENTIAL_CONSTRAINTS 表提供 TiDB 表之间 FOREIGN KEY 关系的信息。

1. USE INFORMATION_SCHEMA;
2. DESC REFERENTIAL_CONSTRAINTS;

输出结果如下：

1. +---------------------------+--------------+------+------+---------+-------+
2. | Field                     | Type         | Null | Key  | Default | Extra |
3. +---------------------------+--------------+------+------+---------+-------+
4. | CONSTRAINT_CATALOG        | varchar(512) | NO   |      | NULL    |       |
5. | CONSTRAINT_SCHEMA         | varchar(64)  | NO   |      | NULL    |       |
6. | CONSTRAINT_NAME           | varchar(64)  | NO   |      | NULL    |       |
7. | UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |      | NULL    |       |
8. | UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |      | NULL    |       |
9. | UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |      | NULL    |       |
10. | MATCH_OPTION              | varchar(64)  | NO   |      | NULL    |       |
11. | UPDATE_RULE               | varchar(64)  | NO   |      | NULL    |       |
12. | DELETE_RULE               | varchar(64)  | NO   |      | NULL    |       |
13. | TABLE_NAME                | varchar(64)  | NO   |      | NULL    |       |
14. | REFERENCED_TABLE_NAME     | varchar(64)  | NO   |      | NULL    |       |
15. +---------------------------+--------------+------+------+---------+-------+
16. 11 rows in set (0.00 sec)

1. CREATE TABLE test.parent (
2. id INT NOT NULL AUTO_INCREMENT,
3. PRIMARY KEY (id)
4. );

6. CREATE TABLE test.child (
7. id INT NOT NULL AUTO_INCREMENT,
8. name varchar(255) NOT NULL,
9. parent_id INT DEFAULT NULL,
10. PRIMARY KEY (id),
11. CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON UPDATE CASCADE ON DELETE RESTRICT
12. );

14. SELECT * FROM REFERENTIAL_CONSTRAINTS\G

输出结果如下：

1. *************************** 1. row ***************************
2. CONSTRAINT_CATALOG: def
3. CONSTRAINT_SCHEMA: test
4. CONSTRAINT_NAME: fk_parent
5. UNIQUE_CONSTRAINT_CATALOG: def
6. UNIQUE_CONSTRAINT_SCHEMA: test
7. UNIQUE_CONSTRAINT_NAME: PRIMARY
8. MATCH_OPTION: NONE
9. UPDATE_RULE: CASCADE
10. DELETE_RULE: RESTRICT
11. TABLE_NAME: child
12. REFERENCED_TABLE_NAME: parent
13. 1 row in set (0.00 sec)