我有表,我已经尝试设置PK FK关系,但我想验证这一点。如何显示PK/FK约束?我看到了这个手册页,但它没有显示示例,我的谷歌搜索也是徒劳的。我的数据库是credentialing1,约束表是practices和cred_insurances。


当前回答

简单地查询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;

其他回答

试着做:

SHOW TABLE STATUS FROM credentialing1;

外键约束列在输出的Comment列中。

你可以用这个:

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'

或者为了更好的格式输出使用:

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'

如果要向information_schema请求,你需要特权。如果你需要简单的键列表,你可以使用这个命令:

SHOW INDEXES IN <tablename>

还有一个oracle做的工具叫mysqlshow

如果你使用——k keys $table_name选项运行它,它将显示键。

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

例子:

╰─➤  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      |         |               |
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

类似于@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;

有用,例如使用ORM。