我有表，我已经尝试设置PK FK关系，但我想验证这一点。如何显示PK/FK约束?我看到了这个手册页，但它没有显示示例，我的谷歌搜索也是徒劳的。我的数据库是credentialing1，约束表是practices和cred_insurances。
当前回答
你可以用这个:
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>
我使用
SHOW CREATE TABLE mytable;
这向您展示了以当前形式重新创建mytable所需的SQL语句。您可以看到所有的列及其类型(如DESC)，但它还显示约束信息(以及表类型、字符集等)。
试着做:
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'
还有一个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 | | |
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
