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;