MySQL 显示表上的约束命令

Show constraints on tables command

提问by Captain Claptrap

I have tables that I've tried setting PK FK relationships on but I want to verify this. How can I show the PK/FK restraints? I saw thismanual page, but it does not show examples and my google search was fruitless also. My database is credentialing1and my constrained tables are practicesand cred_insurances.

我有一些表格，我尝试在其上设置 PK FK 关系，但我想验证这一点。如何显示 PK/FK 限制？我看到了这个手册页，但它没有显示示例，我的谷歌搜索也没有结果。我的数据库是credentialing1，我的受限表是practices和cred_insurances。

回答by Adrian Smith

I use

我用

SHOW CREATE TABLE mytable;

This shows you the SQL statement necessary to receate mytablein its current form. You can see all the columns and their types (like DESC) but it also shows you constraint information (and table type, charset, etc.).

这将向您显示以mytable当前形式重新生成所需的 SQL 语句。您可以看到所有列及其类型（如DESC），但它还显示约束信息（以及表类型、字符集等）。

回答by Resh32

Simply query the INFORMATION_SCHEMA:

只需查询 INFORMATION_SCHEMA：

USE INFORMATION_SCHEMA;
SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "<your_database_name>" 
      AND TABLE_NAME = "<your_table_name>" 
      AND REFERENCED_COLUMN_NAME IS NOT NULL;

回答by nfroidure

The main problem with the validated answer is you'll have to parse the output to get the informations. Here is a query allowing you to get them in a more usable manner :

验证答案的主要问题是您必须解析输出才能获取信息。这是一个查询，允许您以更有用的方式获取它们：

SELECT cols.TABLE_NAME, cols.COLUMN_NAME, cols.ORDINAL_POSITION,
cols.COLUMN_DEFAULT, cols.IS_NULLABLE, cols.DATA_TYPE,
    cols.CHARACTER_MAXIMUM_LENGTH, cols.CHARACTER_OCTET_LENGTH,
    cols.NUMERIC_PRECISION, cols.NUMERIC_SCALE,
    cols.COLUMN_TYPE, cols.COLUMN_KEY, cols.EXTRA,
    cols.COLUMN_COMMENT, refs.REFERENCED_TABLE_NAME, refs.REFERENCED_COLUMN_NAME,
    cRefs.UPDATE_RULE, cRefs.DELETE_RULE,
    links.TABLE_NAME, links.COLUMN_NAME,
    cLinks.UPDATE_RULE, cLinks.DELETE_RULE
FROM INFORMATION_SCHEMA.`COLUMNS` as cols
LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS refs
ON refs.TABLE_SCHEMA=cols.TABLE_SCHEMA
    AND refs.REFERENCED_TABLE_SCHEMA=cols.TABLE_SCHEMA
    AND refs.TABLE_NAME=cols.TABLE_NAME
    AND refs.COLUMN_NAME=cols.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cRefs
ON cRefs.CONSTRAINT_SCHEMA=cols.TABLE_SCHEMA
    AND cRefs.CONSTRAINT_NAME=refs.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS links
ON links.TABLE_SCHEMA=cols.TABLE_SCHEMA
    AND links.REFERENCED_TABLE_SCHEMA=cols.TABLE_SCHEMA
    AND links.REFERENCED_TABLE_NAME=cols.TABLE_NAME
    AND links.REFERENCED_COLUMN_NAME=cols.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cLinks
ON cLinks.CONSTRAINT_SCHEMA=cols.TABLE_SCHEMA
    AND cLinks.CONSTRAINT_NAME=links.CONSTRAINT_NAME
WHERE cols.TABLE_SCHEMA=DATABASE()
    AND cols.TABLE_NAME="table"

回答by pleerock

afaik to make a request to information_schemayou need privileges. If you need simple list of keys you can use this command:

afaik 向您提出请求information_schema需要特权。如果您需要简单的键列表，可以使用以下命令：

SHOW INDEXES IN <tablename>

回答by Abhishek Gupta

You can use this:

你可以使用这个：

select
    table_name,column_name,referenced_table_name,referenced_column_name
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'my_database' 
    and table_name = 'my_table'

Or for better formatted output use this:

或者为了更好的格式化输出使用这个：

select
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'my_database' 
    and table_name = 'my_table'

回答by codaddict

Try doing:

尝试做：

SHOW TABLE STATUS FROM credentialing1;

The foreign key constraints are listed in the Commentcolumn of the output.

外键约束列在输出的注释列中。

回答by simhumileco

Analogous to @Resh32, but without the need to use the USEstatement:

类似于@Resh32，但不需要使用USE语句：

SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "database_name" 
      AND TABLE_NAME = "table_name" 
      AND REFERENCED_COLUMN_NAME IS NOT NULL;

Useful, e.g. using the ORM.

有用，例如使用 ORM。

回答by GardenRouteGold

There is also a tool that oracle made called mysqlshow

oracle 还有一个工具叫mysqlshow

If you run it with the --k keys $table_nameoption it will display the keys.

如果您使用该--k keys $table_name选项运行它，它将显示密钥。

SYNOPSIS
   mysqlshow [options] [db_name [tbl_name [col_name]]]
.......
.......
.......
·   --keys, -k
   Show table indexes.

example:

例子：

╰─?  mysqlshow -h 127.0.0.1 -u root -p --keys database tokens
Database: database  Table: tokens
+-----------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field           | Type             | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id              | int(10) unsigned |                    | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| token           | text             | utf8mb4_unicode_ci | NO   |     |         |                | select,insert,update,references |         |
| user_id         | int(10) unsigned |                    | NO   | MUL |         |                | select,insert,update,references |         |
| expires_in      | datetime         |                    | YES  |     |         |                | select,insert,update,references |         |
| created_at      | timestamp        |                    | YES  |     |         |                | select,insert,update,references |         |
| updated_at      | timestamp        |                    | YES  |     |         |                | select,insert,update,references |         |
+-----------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tokens | 0          | PRIMARY                  | 1            | id          | A         | 2           |          |        |      | BTREE      |         |               |
| tokens | 1          | tokens_user_id_foreign   | 1            | user_id     | A         | 2           |          |        |      | BTREE      |         |               |
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+