显示表命令的约束

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

1> Adrian Smith..：

---

我用

SHOW CREATE TABLE mytable;

这将显示以mytable当前形式重新标记所需的SQL语句.您可以查看所有列及其类型(如DESC),但它还会显示约束信息(以及表类型,字符集等).

---

我不认为它显示了传入的fk关系？例如,`a.col_one引用b.col_one`并且在`show create table b`中将不包含有关上述关系的细节.

---

请注意,从10.2开始的MariaDB将在此返回尴尬的结果.https://jira.mariadb.org/browse/MDEV-15377

2> Resh32..：

---

只需查询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 = "" 
      AND TABLE_NAME = "" 
      AND REFERENCED_COLUMN_NAME IS NOT NULL;

---

这是最好的答案,因为它以您可以以编程方式使用的格式提供结果.当然,您需要添加一个WHERE子句来缩小结果范围

3> 小智..：

---

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

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"

4> pleerock..：

---

afaik向您提出要求information_schema需要特权.如果需要简单的键列表,可以使用以下命令:

SHOW INDEXES IN 

---

警告：这显示索引，而不是约束！

5> codaddict..：

---

尝试做:

SHOW TABLE STATUS FROM credentialing1;

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

---

我在Comment列中只看到表注释.这可能与InnoDB类型有关.

6> Abhishek Gup..：

---

你可以用这个:

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'

---

@noboundaries你在Oracle上尝试这个,问题是关于MySQL