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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     lt your database name gt          AND TABLE NAME     lt your table name gt          AND REFERENCED COLUMN NAME IS NOT NULL

User · Answer

Try doing   SHOW TABLE STATUS FROM credentialing1    The foreign key constraints are listed in the Comment column of the output

User · Answer

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

User · Answer

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  lt tablename gt

User · Answer

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

[mysql] Show constraints on tables command

Examples related to mysql

Examples related to foreign-keys