# mysql – Show constraints on tables command

## The Question :

235 people think this question is useful

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.

438 people think this answer is useful

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.).

119 people think this answer is useful

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;



19 people think this answer is useful

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,
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
WHERE cols.TABLE_SCHEMA=DATABASE()
AND cols.TABLE_NAME="table"



12 people think this answer is useful

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>



8 people think this answer is useful

Try doing:

SHOW TABLE STATUS FROM credentialing1;



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

6 people think this answer is useful

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'



1 people think this answer is useful

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.

0 people think this answer is useful

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