我有表，我已经尝试设置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;
其他回答
简单地查询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;
你可以用这个:
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'
我使用
SHOW CREATE TABLE mytable;
这向您展示了以当前形式重新创建mytable所需的SQL语句。您可以看到所有的列及其类型(如DESC)，但它还显示约束信息(以及表类型、字符集等)。
类似于@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。
试着做:
SHOW TABLE STATUS FROM credentialing1;
外键约束列在输出的Comment列中。
