Show constraints on tables command

Question

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 this manual page, but it does not show examples and my google search was fruitless also. My database is credentialing1 and my constrained tables are practices and cred_insurances.

Answer 1

I use

SHOW CREATE TABLE mytable;

This shows you the SQL statement necessary to receate mytable in 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.).

Answer 2

Simply query the 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;

Answer 3

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"

Answer 4

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

SHOW INDEXES IN <tablename>

Answer 5

Try doing:

SHOW TABLE STATUS FROM credentialing1;

The foreign key constraints are listed in the Comment column of the output.

Answer 6

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'

Answer 7

Analogous to @Resh32, but without the need to use the USE statement:

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.

Answer 8

There is also a tool that oracle made called mysqlshow

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

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      |         |               |
+--------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+